Insert row with certain text based on value in a cell in Column A

So here;s what i am working on, and this is eating up my lunches. I have about 7000 or so rows of data in excel. There is a pattern in data. Starting from stae in some cell and right under that is 'heading B' and 2-3-4 rows down is 'heading C' and so on. For every state there are blocks of information. The problem i am facing is that i want to be able to insert a row with text "SECTION BREAK", when the macro finds keyword "Last Revision".

There are about 200-300 repetitions of this keyword within the sheet and manually going through and inserting rows with text "section break" one by one is very time consuming, Any way excel can do it for me? All the "Last Revision" keywords are located in Column A, but different cells. One could be in cell A2, and the next one in cell A29 and the next one in cell A33 etc etc.

Any help will be apreciated.

Does this work for you?

Sub SectionBreaks()
    Dim LastRow As Long
    Dim s As String
    
    s = "Last Revision"
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        
        For i = LastRow To 2 Step -1
            If Cells(i, 1) = s Then
                Rows(i + 1).Insert
                Cells(i + 1, 1) = "SECTION BREAK"
            End If
        Next i
        .ScreenUpdating = True
    End With
            
End Sub

Originally Posted by BigBas

Does this work for you?

Sub SectionBreaks()
    Dim LastRow As Long
    Dim s As String
    
    s = "Last Revision"
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        
        For i = LastRow To 2 Step -1
            If Cells(i, 1) = s Then
                Rows(i + 1).Insert
                Cells(i + 1, 1) = "SECTION BREAK"
            End If
        Next i
        .ScreenUpdating = True
    End With
            
End Sub

Well, i saved as module and ran it, it didn't do anythhing.

Can you post a copy of your workbook in XLS format so I can determine the differences? It worked on my test book.

Unless, I misunderstood. Is the text Last Revision included in a cell along with other text in that same cell? If so, try the following revised code.

Sub SectionBreaks()
    Dim LastRow As Long
    Dim s As String
    
    s = "*Last Revision*"
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        
        For i = LastRow To 2 Step -1
            If Cells(i, 1) Like s Then
                Rows(i + 1).Insert
                Cells(i + 1, 1) = "SECTION BREAK"
            End If
        Next i
        .ScreenUpdating = True
    End With
            
End Sub

hi, bholabhala, please check attachments, run code "test"