Transpose a variable length list

[HotelLending]

I'm attempting to convert this list..

Column1 Colum2
Bob Apple
Orange
Bannana
Tom Grape
Strawberry
Bill Coconut
Mary Pineapple
Watermellon
etc....

Into this format....

Column1 Column2
Bob Apple, Orange, Bannana
Tom Grape, Strawberry
Bill Coconut
Mary Pineapple, Watermellon

Any help would be appreciated!!

 

[Dave Peterson]

Do this against a copy of your workbook--or close without saving. It destroys
the original data:

Option Explicit
Sub testme02()
Dim wks As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If Trim(.Cells(iRow, "A").Value) = "" Then
.Cells(iRow - 1, "B").Value = .Cells(iRow - 1, "B").Value _
& "," & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

 

[ryguy7272]

Try one of these two macros:
Sub ToRow2()
With ActiveCell
.Offset(1, 0).Resize(9, 1).Copy
.Offset(0, 1).PasteSpecial Transpose:=True
End With
End Sub

Sub ToRow()
With ActiveCell
.Resize(9, 1).Copy
.Offset(0, 1).PasteSpecial Transpose:=True
End With
End Sub

Regards,
Ryan--

 

[Dave Peterson]

This won't work for the OP's original data.

That size of each group was variable.