Delete Specific Items from Filtered Table

Question

I want to have a table filter and remove the items that are not needed. However, when I try to filter the four items that need to be removed, I receive the following error message - Compile error: Named argument not found.

The column I am filtering is 15. The items I want to filter and remove are INF, IWC, ONF, OWC. I only want to see COB.

Sub DeleteRows()
With Sheet2 
    With .ListObjects("Table13").DataBodyRange
        .AutoFilter
        .AutoFilter Field:=15, Criteria1:="INF", "IWC", "ONF", "OWC"
        .EntireRow.Delete
        .AutoFilter
    End With
End With
End Sub

Answer 1

• If autofilter is activated in the table (ListObject), ListObject.Range.AutoFilter (the 4th line in your code) removes autofilter.
• It is important to verify the autofilter status first. If it is activated, proceed to eliminate any existing filters to show all data; otherwise, activate the autofilter.

Microsoft documentation:

Range.SpecialCells method (Excel)

AutoFilter object (Excel)

Sub DeleteRows()
    Dim oFilter As Object, bFilter As Boolean
    Dim rVisRng As Range, rRow As Range
    With Sheet2.ListObjects("Table13")
        If .ShowAutoFilter Then
            bFilter = True
            .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter Field:=15, _
            Criteria1:=Array("INF", "IWC", "ONF", "OWC"), _
            Operator:=xlFilterValues
        On Error Resume Next
        Set rVisRng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rVisRng Is Nothing Then
            For Each rRow In rVisRng.Areas
                rRow.EntireRow.Delete
            Next
        End If
        .AutoFilter.ShowAllData
        If Not bFilter Then .Range.AutoFilter ' disable AutoFilter
    End With
End Sub

Answer 2

Delete Rows From Excel Table

• When filtering by multiple criteria, you need to put them in an array and set the Operator argument's parameter to xlFilterValues.
• Often, when dealing with an Excel table, you don't know (or care about) the index of the column but you know its title e.g. "Abbr". Instead of 15, you can then use .ListColumns("Abbr").Index. Now you can risk-free insert and move columns and the code will still work. On the other hand, of course, if you change the title you're busted.

Sub DeleteRows()
    With Sheet2.ListObjects("Table13")
        If .ShowAutoFilter Then
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter 15, Array("INF", "IWC", "ONF", "OWC"), xlFilterValues
        Dim vrg As Range
        On Error Resume Next
            Set vrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        .AutoFilter.ShowAllData
        If Not vrg Is Nothing Then vrg.Delete xlShiftUp
    End With
End Sub