Using a macro to manipulate textual content in Excel

[FrankNJohnson]

Friends:

I have a spreadsheet with a large amount of textual content. I'm trying to use a macro to manipulate the content into a particular form. I've built a macro that gets me to this point:

(note that the number of rows under each heading is variable and so the last row in the column is also variable).

I want to manipulate the content so it gets to this point:

This is the point where I'm stumped. Would anyone be able to help?

Thanks in advance - it's much appreciated!

Frank

 

[Marc L]

As you forgot to post your VBA procedure which leads 'to this point' so a VBA demonstration as a beginner starter :​

Sub Demo1()
        Dim V, R&, H$
    With [A1].CurrentRegion.Columns
        If .Count > 1 Then Beep: Exit Sub
        V = .Value2
    For R = 1 To .Rows.Count
        If V(R, 1) Like "HEADING *" Then H = V(R, 1): V(R, 1) = Empty Else V(R, 1) = H
    Next
       .Item(1).Insert
       .Item(0).Value2 = V
    End With
End Sub

 

[Peter_SSs]

What identifies a heading as a heading?
Is it the word "HEADING" or is the fact that the text is UPPER CASE or something else?
If upper case, then you could try this with a copy of your workbook.

Sub Repeat_Headings()
  Columns(1).Insert
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
     .FormulaR1C1 = "=IF(EXACT(R[-1]C[1],UPPER(R[-1]C[1])),R[-1]C[1],IF(EXACT(RC[1],UPPER(RC[1])),"""",R[-1]C))"
     .Value = .Value
     .Columns.AutoFit
  End With
End Sub

BTW, for better help in the future I suggest that you ..

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.