How to get certain Items out of Range in VBA

Question

I have a large Range which has 13138 cells in it, just like this:

     **(A)**
   (1) Item:
   (2) HO
   (3) Item:
   (4) HO
   (5) Item:
   (6) HO
   (7) Item:
   (8) HO
   (9) Item:
   (10) MO
   (11) Item:
   (12) MFG
   (13) MO
   (14) Item:
   (15) MO
   (16) Item:
   (17) HO
   (18) Item:
   (19) MFG
   (20) MO
   (21) Item:
   (22) MFG
   (23) Item:
   (24) MFG

I need all the cells between 2 "Item:" cells grouped like

HO
HO
HO
HO
MO
MFG, MO
MO
MO
HO

and so on. How can this be achieved?

Answer 1

Try with below code

---

Option Explicit
Sub test()
    Dim lastrow As Long, i As Long
    Dim out As String
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lastrow
        Do While Range("A" & i).Value <> "Item"
            If out = "" Then
                out = Range("A" & i).Value
            Else
                out = out & ", " & Range("A" & i).Value
            End If
            If i < lastrow Then
                i = i + 1
            Else
                Exit Do
            End If
        Loop
        Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1).Value = out
        out = ""
    Next i
End Sub

---

Results will be in B column. See the snap below