在包含搜索文本的行之后删除Excel中的所有行

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?

如何编写VBA脚本来搜索“终止”并删除之后的所有行？

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.

然而，到目前为止，我将这两者结合起来的尝试却毫无结果。

1 个解决方案

#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

如何从这里确定lastRow

#1