CODEWITHSUNDEEP
vbaexcel
Andrew Martin
Andrew Martin

Reputation: 5761

Deleting all rows in Excel after one containing searched for text

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.

Upvotes: 1

Views: 1046

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

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

Upvotes: 1

Related Questions