首页 文章

如何在Excel中使用VBA搜索单词然后删除整行？ [重复]

提问于
浏览 次
2

这个问题在这里已有答案：

有人请帮忙 . 我正在尝试编写一个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"))

我很乐意提供帮助 .

excel vba excel-vba

3 回答

  • 5

    干净简单，诀窍！ ;）

    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
    回复于
  • 1

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

    假设您的工作表看起来像这样

    enter image description here

    您可以使用Excel执行脏工作;）使用 .AutoFilter

    看到这段代码

    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:

    enter image description here

    回复于
  • 2

    另一种使用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

    下面的示例类似，但它从底部开始，以相反的顺序向上移动 . 它一次删除每一行而不是一次删除所有行 .

    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
    回复于

相关问题