Excel VBA技术问询：如何遍历单元格区域直至遇到空单元格？

Hey there! Let's tackle this Excel VBA problem step by step—since you're not a pro dev, I'll keep things clear and actionable. Here's a solution tailored exactly to your workflow:

We'll break this into simple parts: referencing the source workbook, finding the last used row in your current sheet (traversing until we hit an empty cell), pasting the data, and ensuring your D/E column formulas apply to the new rows.

Step-by-Step Explanation & Complete Code #

Here's a fully commented script that does everything you need:

Sub PasteDataToBottom()
    Dim sourceWorkbook As Workbook
    Dim targetSheet As Worksheet
    Dim lastRowSource As Long
    Dim lastRowTarget As Long
    
    ' Set the target sheet (the one in your current workbook where you want to paste data)
    Set targetSheet = ThisWorkbook.Worksheets("YourSheetName") ' Replace with your actual sheet name
    
    ' Option 1: Hardcode the source workbook path (replace with your file's path)
    ' Set sourceWorkbook = Workbooks.Open("C:\YourFolder\SourceFile.xlsx")
    
    ' Option 2: Let you pick the source file interactively (better for flexibility)
    Dim sourceFilePath As String
    sourceFilePath = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xlsx;*.xls), *.xlsx;*.xls", _
        Title:="Select the Source Workbook")
    If sourceFilePath = "False" Then Exit Sub ' Exit if you cancel the file picker
    Set sourceWorkbook = Workbooks.Open(sourceFilePath)
    
    ' Find the last row with data in the source workbook's A-C columns
    lastRowSource = sourceWorkbook.Worksheets("SourceSheetName").Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Find the last row with data in your target sheet (stops at the first empty cell in column A)
    lastRowTarget = targetSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Copy A-C data from source to the next empty row in target
    sourceWorkbook.Worksheets("SourceSheetName").Range("A1:C" & lastRowSource).Copy _
        targetSheet.Range("A" & lastRowTarget + 1)
    
    ' Update the last row count after pasting new data
    lastRowTarget = targetSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Fill down your D/E column formulas to the new rows
    ' This assumes your formulas start at D2/E2 (adjust if your first formula row is different)
    targetSheet.Range("D2:E" & lastRowTarget).FillDown
    
    ' Close the source workbook without saving changes (adjust to True if you need to save)
    sourceWorkbook.Close SaveChanges:=False
    
    MsgBox "Data pasted and formulas updated successfully!", vbInformation
End Sub

Key Tips for Non-Developers #

• Replace placeholders: Swap out "YourSheetName" and "SourceSheetName" with the actual names of your sheets.
• How the "traverse to empty cell" works: The line Cells(Rows.Count, "A").End(xlUp).Row acts just like pressing Ctrl+Up from the very bottom of column A—it stops at the last cell with data, which is exactly your need to find the end of your current table.
• Formula handling: The FillDown method copies your existing D/E formulas to all new rows. Make sure your original formulas use relative references (like =A2+B2 instead of =$A$2+$B$2) so they adjust correctly for each new row.
• File picker vs hardcode: Use Option 2 (interactive file picker) if you need to select different source files each time—no need to edit the code every time!

Quick Troubleshooting #

• If you get a "file not found" error, double-check the file path (if using Option 1) or make sure you're selecting the correct file.
• If formulas don't fill correctly, verify your original D/E formulas are set up with relative references.
• Ensure neither workbook is password-protected—protected sheets can block copying/pasting.

内容的提问来源于stack exchange，提问作者shurley