Deleting all rows in Excel after one containing searched for text

Question

I have a spreadsheet with a varying number of rows in it. At the bottom of the useful information on the spreadsheet is a row called "Terminations", followed by a varying number of rows none of which I'm interested in.

How can I write a VBA script to search for "Terminations" and delete ALL rows after it?

I can search for "Terminations" like so:

  Cells.Find(What:="Terminations", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

And I can delete rows like so:

Rows("245:246").Select
    Selection.Delete Shift:=xlUp

However, my attempts thus far to combine these two has been fruitless.

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

Try this one:

Sub test()
    Dim rng As Range
    Dim lastRow As Long

    'change Sheet1 to suit
    With ThisWorkbook.Sheets("Sheet1")
        'find Terminations
        Set rng = .Cells.Find(What:="Terminations", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False)
        'if Terminations NOT found - exit from sub
        If rng Is Nothing Then Exit Sub
        'find last row
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastRow = 1
        End If
        'I use lastRow + 1 to prevent deletion "Terminations" when it is on lastrow
        .Range(rng.Row + 1 & ":" & lastRow + 1).Delete Shift:=xlUp
    End With
End Sub

How to determine lastRow from here

edited May 23 '17 at 11:52

Community

1
1

answered May 01 '14 at 10:45

Dmitry Pavliv

35,333
13
79
80

Unfortunately, even when I change the "ThisWorkbook.Sheets()" part to the name of my sheet, I get a "subscript out of range" error, highlighting that row (my sheet is named SysRat). – Andrew Martin May 01 '14 at 10:48
it should be `With ThisWorkbook.Sheets("SysRat")`. Also check you sheet name for additional leading/trailing spaces – Dmitry Pavliv May 01 '14 at 10:50
Yeah, I've changed the line as appropriate. There are no other spaces in or around the title - I just can't get it past that line. – Andrew Martin May 01 '14 at 10:50
Are you sure your worksheet with name "SysRat" is in workbook _where code is places_? – Dmitry Pavliv May 01 '14 at 10:52
Think I know why - all my macros are stored on my "Personal Workbook", but I usually have that hidden and work on the _open_ workbook – Andrew Martin May 01 '14 at 10:52
in that case change `ThisWorkbook` to `ActiveWorkbook` or just use `With Sheets("SysRat")` – Dmitry Pavliv May 01 '14 at 10:53
1

That's the ticket. Thank you very much. – Andrew Martin May 01 '14 at 10:58

Deleting all rows in Excel after one containing searched for text

1 Answers1