Removing single autofilter from list

Question

I have a filtered list, and would like to roll back filter criteria depending on what is selected from the list box in cell C11. Because there are going to be multiple columns to filter, simply using .autofilter = false would not achieve the desired effect, as it would roll back all filters and not just for a given column. Is there a way to turn off a single columns filter?

If Target.Address = "$C$11" Then
    If Range("C11").Value = "All" Then
        With Sheets("Provider Front End")
        .Range("B17:D17").AutoFilter field:=3, Critieria1:= 'turn off this single field?
    End With
Else
    With Sheets("Provider Front End")
        .Range("B17:D17").AutoFilter
        .Range("B17:D17").AutoFilter field:=3, Criteria1:=Range("C11").Value
    End With
End If
End If

Scott Holtzman Scott Holtzman · Accepted Answer · 2015-12-09T13:26:52

To turn off a filter for a specific field leave the Criteria1 argument blank.

.Range("B17:D17").AutoFilter field:=3

For example, the following code takes the following steps:

Filters the first column for the value 12
Filters the second column for the value 14
Takes the filter off the second column, so it's back to just 12 on the first column.

Example Code:

Range("A1:D6").AutoFilter 1, 12
Range("A1:D6").AutoFilter 2, 14
Range("A1:D6").AutoFilter 2

Removing single autofilter from list

1 Answers