excel根据条件从表宏中删除行 [英] excel Delete rows from table Macro based on criteria

问题描述

我的问题:我正在尝试删除 AH 列中的表中的行，并且条件为"Del"，因此 AH 列中的任何单元格都想删除整个该表中的行.

My Question: I am trying to delete rows in a table in column AH and Criteria is "Del" so any cell in column AH, I want to delete entire row in that table.

我尝试了许多不同的代码，并且由于要删除10000多个行，所以大多数代码要花很长时间.我在一个网站上找到了此代码，但是在 If Intersect 行中，我收到了错误下标Error9范围:

I tried so many different codes and most take forever as I have 10000+ rows to delete. I found this code from a site, but I am getting an error subscript out of range Error9 from the If Intersect line:

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

然后我将sub命名如下:

Then I called the sub as below:

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

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

Any help would be great. Thank you.

推荐答案

您应该能够只使用 AutoFilter 而不是循环.它要快得多.

You should be able to just use AutoFilter instead of a loop. It is much faster.

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

这篇关于excel根据条件从表宏中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！