vba - 创建 Excel 宏以一次删除多行

Question

我在网上找到了一个代码，并想对其进行编辑。代码在 VBA 中，我希望宏代码删除多行而不是一行。这是代码:

Sub findDelete()
    Dim c As String
    Dim Rng As Range

    c = InputBox("FIND WHAT?")

    Set Rng = Nothing

    Set Rng = Range("A:A").Find(what:=c, _
        After:=Range("A1"), _
        LookIn:=xlFormulas, _
        lookat:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    Rng.EntireRow.Delete shift:=xlUp
End Sub

Answer 1

不要使用查找，而是使用 Autofilter并删除 VisibleCells

Sub findDelete()

Dim c As String, Rng As Range, wks as Worksheet

c = InputBox("FIND WHAT?")

Set wks = Sheets(1) '-> change to suit your needs
Set Rng = wks.Range("A:A").Find(c, After:=Range("A1"), LookIn:=xlFormulas, _
                            lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False)

If Not Rng Is Nothing Then

    With wks

        .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).AutoFilter 1, c
        Set Rng = Intersect(.UsedRange, .UsedRange.Offset(1), .Range("A:A")).SpecialCells(xlCellTypeVisible)
        Rng.Offset(1).EntireRow.Delete

    End With

End If

End Sub

编辑

要将 InputBox 替换为多个值以查找/删除，请执行以下操作:

Option Explicit

Sub FindAndDeleteValues()

Dim strValues() as String

strValues() = Split("these,are,my,values",",")

Dim i as Integer

For i = LBound(strValues()) to UBound(strValues())

    Dim c As String, Rng As Range, wks as Worksheet
    c = strValues(i)

    '.... then continue with code as above ...

Next

End Sub