如何用Excel VBA循环合并带换行的多单元格为带换行的单单元格

Hey Patou, I totally get where you're coming from—diving into VBA as a beginner can hit you with a wall fast, especially when you realize how much there is to learn. Let's get this sorted for you with a straightforward, commented code that does exactly what you need.

What we're aiming to do: #

• Loop through every row in your sheet
• Split each cell in columns A-D by line breaks (Chr(10)) into separate chunks
• For each line break position, combine the chunks from A-D with spaces in between
• Put the combined chunks back into column E, keeping the same number of line breaks as the original columns
• Handle empty D column automatically

The VBA Code #

Open your Excel workbook, press Alt + F11 to open the VBA editor, insert a new module (right-click your workbook in the Project Explorer > Insert > Module), and paste this code:

Sub MergeColumnsWithLineBreaks()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim arrA() As String, arrB() As String, arrC() As String, arrD() As String
    Dim combinedLines() As String
    
    ' Set this to your actual worksheet name (e.g., Sheet1)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in column A (adjust if your data starts elsewhere)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 2 (assuming row 1 is headers)
    For i = 2 To lastRow
        ' Split each column's content into arrays using line breaks as delimiter
        arrA = Split(ws.Cells(i, "A").Value, Chr(10))
        arrB = Split(ws.Cells(i, "B").Value, Chr(10))
        arrC = Split(ws.Cells(i, "C").Value, Chr(10))
        arrD = Split(ws.Cells(i, "D").Value, Chr(10))
        
        ' Get the number of lines (since all columns have same line count)
        Dim lineCount As Long
        lineCount = UBound(arrA) + 1
        
        ' Resize the combined lines array to match the line count
        ReDim combinedLines(0 To lineCount - 1)
        
        ' Loop through each line position and combine the chunks
        For j = 0 To lineCount - 1
            ' Combine A-D chunks with spaces; handle empty D column gracefully
            combinedLines(j) = arrA(j) & " " & arrB(j) & " " & arrC(j) & " " & arrD(j)
            ' Optional: Trim extra spaces if D column is empty (uncomment below)
            ' combinedLines(j) = Trim(combinedLines(j))
        Next j
        
        ' Join the combined lines back into a single string with line breaks
        ws.Cells(i, "E").Value = Join(combinedLines, Chr(10))
        
        ' Turn on wrap text for column E so line breaks show up
        ws.Cells(i, "E").WrapText = True
    Next i
    
    MsgBox "Processing complete!", vbInformation
End Sub

Key Explanations for Beginners: #

• Split(): This function takes a string and splits it into an array using the delimiter you specify (here, Chr(10) which is the line break character in Excel cells).
• UBound(arrA) + 1: Gets the number of lines in the array (since arrays start at 0 by default).
• Join(): Takes an array of strings and combines them into one string, using the delimiter you choose (again, Chr(10) to keep the line breaks).
• The outer loop (For i = 2 To lastRow) goes through every row with data. The inner loop handles each line break position in the row.
• If you want to remove extra spaces from the end of each line (since D column is empty), uncomment the Trim() line.

How to Use: #

1. Replace "Sheet1" with the name of your actual worksheet.
2. Make sure your data starts at row 2 (if your headers are in row 1). If not, adjust the For i = 2 To lastRow line to start at your first data row.
3. Run the macro (press F5 in the VBA editor, or assign it to a button in Excel for easier access).

This should handle all your rows in one go, exactly matching the example you shared. Let me know if you run into any snags!

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