How to merge rows so whole sheet shows “text” and “url” one under the other

[jamescook]

There are a bunch of highlighted yellow text. I want to merge rows e.g. 6, 7, 8 and 9 into 6. Similarly 17 & 18 into 17. So and so forth

So the final output I want is

1. Text
2. URL
3. Text
4. URL
5. Text
6. URL

Note: 1. This is sample file but it could have hundreds of rows with uneven rows to be merged.

I have used the following formula ". =MOD(ROW(),2)" and then filter so only "Comments" shows then manually go through rows and merge manually. I want an automatic solution.

 

[DanteAmor]

Try this, the results in column B

Sub Merge_Rows()
  Dim i As Long, j As Long, a As Variant, b As Variant
  a = Range("A2", Range("A" & Rows.Count).End(3))
  ReDim b(1 To UBound(a), 1 To 1)
  j = 1
  For i = 1 To UBound(a)
    If LCase(Left(a(i, 1), 4)) <> LCase("http") Then
      b(j, 1) = b(j, 1) & a(i, 1) & Chr(10)
    Else
      If Right(b(j, 1), 1) = Chr(10) Then b(j, 1) = Left(b(j, 1), Len(b(j, 1)) - 1)
      j = j + 1
      b(j, 1) = a(i, 1)
      j = j + 1
    End If
  Next
  Range("B2").Resize(j, 1).Value = b
End Sub

 

[jamescook]

Hi DanteAmor,

Sorry for the late response.

Fantastic! It just works like a charm. Thanks a million. You are simply superb.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.