Excel列合并问题求助：Sheet1的E、G列合并至Sheet2最后一行失败

Hey Chris, let's get that column merging issue sorted out! It sounds like two key problems are tripping you up: you're only capturing the last row from Sheet1 instead of all relevant rows, and you're not correctly targeting the next empty row in Sheet2's Column B. Here's a step-by-step solution with working code:

Root Causes of Your Current Issue #

• You’re probably referencing only the final row of Sheet1 (without looping through all rows with data)
• Your method to find the next empty row in Sheet2 might be incorrect, leading to misplaced data

Working VBA Code #

Sub MergeAndAppendColumns()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, nextRow2 As Long
    Dim i As Long
    
    ' Set references to your worksheets (avoid using ActiveSheet!)
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' Get the last row with data in Sheet1 Column E
    lastRow1 = ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row
    
    ' Get the next empty row in Sheet2 Column B (add 1 to start after last existing row)
    nextRow2 = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row + 1
    
    ' Loop through all rows in Sheet1 with data in Column E
    ' Start at i=2 if you have a header row; change to i=1 if no header
    For i = 2 To lastRow1
        ' Merge Column E and G from Sheet1, write to Sheet2 Column B
        ' Add a separator (like space/comma) between values if needed
        ws2.Cells(nextRow2, "B").Value = ws1.Cells(i, "E").Value & " " & ws1.Cells(i, "G").Value
        
        ' Move to the next empty row in Sheet2 for the next entry
        nextRow2 = nextRow2 + 1
    Next i
    
    ' Optional: Inform user when done
    MsgBox "Column merging and appending complete!", vbInformation
End Sub

Key Details to Note #

• Worksheet References: Using ThisWorkbook.Sheets("Sheet1") ensures we’re targeting the correct workbook, even if other workbooks are open.
• Finding Last Rows: ws1.Rows.Count is safer than Rows.Count because it uses the specific worksheet’s row limit (important for older Excel versions).
• Header Handling: If Sheet1 has a header row, start the loop at i=2 to skip it. If there’s no header, change it to i=1.
• Custom Separators: If you want a comma, dash, or other separator between Column E and G values, modify the merge line—for example:

  ws2.Cells(nextRow2, "B").Value = ws1.Cells(i, "E").Value & ", " & ws1.Cells(i, "G").Value
  

How to Use This Code #

1. Open your Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer > Insert > Module
4. Paste the code above into the module
5. Press F5 to run the macro, or assign it to a button for easier access

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