VBA to merge cells in column B

[robertvdb]

I need help with the following (see enclosed screenshot):

cell B2 needs to be merged with the cells below, until a new name appears in column A. Then cell B5 needs to be merged with B6, etcetera.

I have thousands of names in A, so there is need for VBA to loop thru the whole thing.

Thanks in advance.

 

[Joe4]

Try this:

Sub MyCombineRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Set initial row at 2
    r = 2
   
'   Loop through rows
    Do
'       Exit if row B is empty
        If Cells(r, "B") = "" Then Exit Do
'       Check to see if column A is empty
        If Cells(r, "A") = "" Then
'           Combine value in row B with value above
            Cells(r - 1, "B") = Cells(r - 1, "B") & vbCrLf & Cells(r, "B")
'           Delete current row
            Rows(r).Delete
        Else
'           Increment row counter
            r = r + 1
        End If
    Loop
   
    Application.ScreenUpdating = True
   
End Sub

 

[robertvdb]

Thanks Joe. Works perfectly !

 

[Joe4]

You are welcome.