CODEWITHSUNDEEP
excelautofiltervba
SFro
SFro

Reputation: 131

Removing single autofilter from list

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

Upvotes: 0

Views: 2085

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

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:

  1. Filters the first column for the value 12
  2. Filters the second column for the value 14
  3. 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

Upvotes: 2

Related Questions