VBA: Find row containing specific text string, delete all rows after?

[OddThomas]

I'm working with Excel worksheets generated by financial software, in this instance reports in a set format.

These reports always contain a specific (unique) text string somewhere in column B. The number of rows before it appears is variable. There is always additional data below this row. I need to keep everything down to this row and the one below (which is blank in column B), then delete everything else that follows.

I've managed to get as far as this, which deletes a single (the first) unnecessary row, but not to do the whole range:

Sub DeleteEndRows()
Dim LastRow As Long, x As Long
Dim ws As Worksheet
Set ws = ActiveSheet

LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For x = 1 To LastRow
    If ws.Cells(x, "B") = "TEXT" Then
       ws.Rows(x + 2).Delete
    End If
Next x
 
End Sub

Total newbie to VBA (today!) so gentle hand holding much appreciated.

 

[Joe4]

Welcome to the Board!

Try this:

Sub DeleteEndRows()

Dim LastRow As Long, x As Long
Dim ws As Worksheet
Set ws = ActiveSheet

LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For x = 1 To LastRow
    If ws.Cells(x, "B") = "TEXT" Then
       ws.Rows(x + 2 & ":" & LastRow).Delete
       Exit For
    End If
Next x
 
End Sub