Variation on how to combine values from multiple rows into a single row in Excel?

Question

Hi I am trying to adapt the excellent VBA that was posted previously to go through data and take all lines with the same id in column 1 and add them all into one row for each. Trying to get from this:

Start point

To this

End point

but the adapted code below only ever combines 2 rows even if there are more than two with the same id in column 1.

Sub CombineInvoices()
Dim currentRow As Long
Dim currentCol As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row 
currentCol = 4
For currentRow = LastRow To 2 Step -1
    If Cells(currentRow, 1) = Cells(currentRow - 1, 1) Then
        Range(Cells(currentRow, 1), Cells(currentRow, 4)).Copy Destination:=Range(Cells(currentRow - 1, currentCol + 1), Cells(currentRow - 1, currentCol + 4))
        Rows(currentRow).EntireRow.Delete
    End If
Next
currentCol = currentCol + 4
End Sub

All help really gratefully received.

if you move the currentCol = currentCol + 4 statement

score 0 · Answer 1 · answered Sep 30 '16 at 13:04

Managed to work it out. I was deleting the rows as I was going separated the copy and delete statements as below and all is working! Yay!

Sub CombineRowsRevisitedStep()
Dim currentRow As Long
Dim currentCol As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
currentCol = 4
For currentRow = LastRow To 2 Step -1
    If Cells(currentRow, 1) = Cells(currentRow - 1, 1) Then
        Range(Cells(currentRow, 1), Cells(currentRow, currentCol)).Copy Destination:=Range(Cells(currentRow - 1, 4), Cells(currentRow - 1, currentCol + 30))
    currentCol = currentCol + 4
    End If
Next
For currentRow = LastRow To 2 Step -1
    If Cells(currentRow, 1) = Cells(currentRow - 1, 1) Then
        Rows(currentRow).EntireRow.Delete
    End If
Next
End Sub

Variation on how to combine values from multiple rows into a single row in Excel?

1 Answers1