Category: Tables

It’s a pretty common requirement to filter out some values in an Excel table, then delete those rows from the table. It should be straightforward to do this with a little VBA, but it seems to catch lots of people out! Here’s one simple method:

Sub DelFilter()
    With MySheet
        With .ListObjects("MyTable").DataBodyRange
            .AutoFilter
            .AutoFilter Field:=4, Criteria1:="MyCriteria"
            .EntireRow.Delete
            .AutoFilter
        End With
    End With
End Sub

You just need to change the sheet codename, the table name, the field index number, and the criteria value.

It would be simple to replace the user selected values with variables or parameters, so the code becomes more reusable:

Sub TestDelFilter()
    Call DelFilterParam(Sheet1, "MyTable", 4, "MyCriteria")
End Sub


Sub DelFilterParam( _
    ByVal wsSheet As Worksheet, _
    ByVal stTable As String, _
    ByVal iField As Integer, _
    ByVal vCriteria As Variant)
    
    With wsSheet
        With .ListObjects(stTable).DataBodyRange
            .AutoFilter
            .AutoFilter Field:=iField, Criteria1:=vCriteria
            .EntireRow.Delete
            .AutoFilter
        End With
    End With
End Sub

This is a simple example, applying a very simple filter. But it’s usually the method for deleting the filtered rows which catches people out, and this approach makes it easy.