excel 根据条件从表宏中删除行

Question

我的问题:我正在尝试删除 AH 列中的表中的行并且标准是“Del”，所以列 AH 中的任何单元格，我想删除该表中的整行。

我尝试了这么多不同的代码，大多数都需要很长时间，因为我有 10000 多行要删除。我从网站上找到了此代码，但收到错误 subscript out of range Error9来自 If Intersect线:

Private Sub deleteTableRowsBasedOnCriteria(tbl As ListObject, _
                                           columnName As String, _
                                           criteria As String)

    Dim x As Long, lastrow As Long, lr As ListRow
    lastrow = tbl.ListRows.Count
    For x = lastrow To 1 Step -1
        Set lr = tbl.ListRows(x)
        If Intersect(lr.Range, tbl.ListColumns(columnName).Range).Value = criteria Then
            'lr.Range.Select
            lr.Delete
        End If
    Next x
End Sub

然后我如下调用子:

Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table4")
Call deleteTableRowsBasedOnCriteria(tbl, "AH", "Del")

任何帮助都会很棒。谢谢你。

Answer 1

您应该可以使用 AutoFilter而不是一个循环。它要快得多。

Sub Macro1()
    Dim wks As Worksheet
    Dim tbl As ListObject
    Dim lastRow As Long
    Dim rng As Range

    Set wks = ActiveWorkbook.Sheets("Sheet1")

    Set tbl = wks.ListObjects("Table4")

    ' Filter and delete all rows that have "Del" in it
    With tbl.Range
        ' Switch off the filters before turning it on
        .AutoFilter
         ' Field:=34 must be equal to the column where you have the criteria in
        .AutoFilter Field:=34, Criteria1:="Del"

        ' Set the range for the filtered cells
        Set rng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        .AutoFilter ' Turn off the filter
        rng.Delete ' Delete the filtered cells
    End With
End Sub