Combine Cell Data to single cell between empty cells

[arpirnat]

Trying to find a way to combine the data in a random number of cells to a single cell. The data would combine between empty cells in column B. Example below... ideally it would only display in column C in the same row that column A has a value.

1​	ABC	ABC DEF GHI
	DEF
	GHI
2​	ABC	ABC DEF GHI JKL MNO PQR STU
	DEF
	GHI
	JKL
	MNO
	PQR
	STU
3​	ABC	ABC DEF
	DEF
4​	ABC	ABC

 

[mrshl9898]

Try:

Sub combine()

Dim sht As Worksheet
Dim LastRow As Long
Dim Rownum As Long
Dim FirstRow As Long
Dim mystr As String

Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
Rownum = 1

Do Until Rownum > LastRow + 1
    FirstRow = Rownum
        Do Until Cells(Rownum, 2) = ""
            mystr = mystr & Cells(Rownum, 2) & Chr(13) & Chr(10)
            Rownum = Rownum + 1
        Loop
    Cells(FirstRow, 3) = Left(mystr, Len(mystr) - 1)
    Rownum = Rownum + 1
    mystr = ""
Loop

End Sub

 

[arpirnat]

Seems to do the trick, thanks much!