Combine multiple rows into one
- by Jim
I am trying to combine multiple rows of data into one. Column A contains the value on which the groupings will be based -- rows whose Column A values match will be combined into one row. My range extends from column A through X so I need a matching row of data to start in column Y.
Example:
+--------------+
¦ 1001 ¦ A ¦ C ¦
¦ 1001 ¦ B ¦ D ¦
¦ 1002 ¦ A ¦ E ¦
¦ 1002 ¦ B ¦ F ¦
¦ 1002 ¦ C ¦ G ¦
+--------------+
Desired Result:
+------------------------------+
¦ 1001 ¦ A ¦ C ¦ B ¦ D ¦ ¦ ¦
¦ 1002 ¦ A ¦ E ¦ B ¦ F ¦ C ¦ G ¦
+------------------------------+
The VBA code I am currently using is not taking the entire contents of the matched row. It is only taking the data in the 2nd column and moving it up.
VBA Code:
Sub Mergeitems()
Dim cl As Range
Dim rw As Range
Set rw = ActiveCell
Do While rw <> ""
' for each row in data set
' find first empty cell on row
Set cl = rw.Offset(0, 1)
Do While cl <> ""
Set cl = cl.Offset(0, 1)
Loop
' if next row needs to be processed...
Do While rw = rw.Offset(1, 0)
cl = rw.Offset(1, 1) ' move the data
Set cl = cl.Offset(0, 1) ' update pointer to next blank cell
rw.Offset(1, 0).EntireRow.Delete xlShiftUp ' delete old data
Loop
' next row
Set rw = rw.Offset(1, 0)
Loop
End Sub
