Insert two rows after data change, then add heading in row and format

[JessicaR]

Hello everyone

I have just started using VBA and have had a little success on the easier things, but I am now struggling with the following scenario.
I am trying to use VBA to write a Macro the will format a list of data which has been exported into excel (the number of rows are never the same) and I have not been able to figure out how to write the VBA code so that the Macro will do three different things to a cell/row as listed below and recognises that it should use the second of the newly inserted rows.

I basically trying to write the Macro to do the following:

1) Insert two rows after each data change in column A
2) In the second of the newly inserted rows I need to put in headings for each column (A:J)
3) I then need to Bold and colour each cell

I am also struggling with the fact that the number of rows are not constant with each download

I would be grateful for any help you could give me, as I have been trying to figure this out for two days and can only get little bits to work

S

 

[John_w]

Maybe this will help, but I've made 3 assumptions:

1. Row 1 contains column headings so the data starts in row 2 (A2).
2. For 2) you haven't said what the headings are or where they come from. From row 1?
3. For 3) you haven't said which cells you want to change to bold and colour. The 2nd inserted row?

Sub InsertRows()

    Dim r As Long
    
    'Start at row 2 because row 1 contains the column headings
    
    r = 2
    
    Do While Not IsEmpty(Cells(r, "A").Value) And Not IsEmpty(Cells(r + 1, "A").Value)
               
        If Cells(r, "A") <> Cells(r + 1, "A") Then
        
            'Column A value has changed - insert 2 rows below
            
            Rows(r + 1 & ":" & r + 2).Insert Shift:=xlDown
            
            'Copy headings from row 1 to 2nd inserted row
            
            Rows(1).Copy Rows(r + 2)
        
            'Set font of 2nd inserted row to bold red
            
            Rows(r + 2).Font.Bold = True
            Rows(r + 2).Font.ColorIndex = 3
       
            r = r + 2
            
        End If
    
        r = r + 1
        
    Loop
    
End Sub

 

[JessicaR]

Thank-you very, very much. It works a treat