如何使用 VBA 在 Excel 中搜索单词，然后删除整行？

How do I search for a word in Excel Using VBA and then Delete the entire row?

提问by user2865838

Someone please help. I'm trying to write a VBA code that searches for a particular word "DR" in my excel worksheet column "D" and then delete the entire row. There are lots of occurrences of the particular word in the worksheet. All I want to do is to search for the these occurrences and then delete the entire rows that contains those words. My problem is that I'm not sure what loop structure to use. Below is the code I'm using.

有人请帮忙。我正在尝试编写一个 VBA 代码，在我的 excel 工作表列“D”中搜索特定单词“DR”，然后删除整行。工作表中出现了很多特定单词。我想要做的就是搜索这些出现，然后删除包含这些词的整行。我的问题是我不确定要使用什么循环结构。下面是我正在使用的代码。

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

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


   ActiveCell.EntireRow.Delete

Loop While (Cells.Find(What:="DR"))

I'll be glad for an assistance.

我很乐意提供帮助。

采纳答案by CustomX

Clean and simple, does the trick! ;)

干净和简单，做的伎俩！;)

LastRow = Cells(Rows.Count, "D").End(xlUp).Row

For i = LastRow To 1 Step -1
   If Range("D" & i).Value = "DR" Then
      Range("D" & i).EntireRow.Delete
   End If
Next i

回答by Siddharth Rout

Another Way (the fastest way)

另一种方式（最快的方式）

Let's say your worksheet looks like this

假设您的工作表如下所示

You can use the Excel to do the dirty work ;) Use .AutoFilter

您可以使用 Excel 来做脏活 ;) 使用 .AutoFilter

See this code

看到这个代码

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim strSearch As String

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    '~~> Search Text
    strSearch = "DR"

    With ws
        '~~> Remove any filters
        .AutoFilterMode = False

        lRow = .Range("D" & .Rows.Count).End(xlUp).Row

        With .Range("D1:D" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

Output:

输出：

回答by B Hart

Also another method using Find...

还有另一种使用 Find 的方法...

Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim sFirstAddress As String

strSearch = "DR"
Set rDelete = Nothing

Application.ScreenUpdating = False

With Sheet1.Columns("D:D")
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
    If Not rFind Is Nothing Then
        sFirstAddress = rFind.Address
        Do
            If rDelete Is Nothing Then
                Set rDelete = rFind
            Else
                Set rDelete = Application.Union(rDelete, rFind)
            End If
            Set rFind = .FindNext(rFind)
        Loop While Not rFind Is Nothing And rFind.Address <> sFirstAddress

        rDelete.EntireRow.Delete

    End If
End With
Application.ScreenUpdating = True
End Sub

The below example is similar but it starts at the bottom and works its way to the top in reverse order. It deletes each row at a time instead of all at once.

下面的例子是类似的，但它从底部开始，并以相反的顺序工作到顶部。它一次删除每一行而不是一次删除所有行。

Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String

strSearch = "DR"
Set rDelete = Nothing

Application.ScreenUpdating = False

With Sheet1.Columns("D:D")
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Application.ScreenUpdating = True
End Sub