VBA question: How do I reset filters before setting a new filter?

[bigpat]

Hi,

Columns A:G in my spreadsheet contain "Yes" or can be blank and there may be rows where there is more than one "Yes".

I have a dropdown in cell K2 where I can select the names of one of these columns and then I want to filter the data based on that column.

There's probably a better way, but this works - kind of!

If Range("K2").Value = "Adult and Specialtist Rehabilitation" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=1, Criteria1:="Yes"
If Range("K2").Value = "Adult Community" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=2, Criteria1:="Yes"
If Range("K2").Value = "Children and Families" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=3, Criteria1:="Yes"
If Range("K2").Value = "Corporate and Other" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=4, Criteria1:="Yes"
If Range("K2").Value = "Dental" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=5, Criteria1:="Yes"
If Range("K2").Value = "Learning Disability" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=6, Criteria1:="Yes"
If Range("K2").Value = "Tooth and Tissue Bank" Then ActiveSheet.Range("$A$1:$AA$10001").AutoFilter Field:=7, Criteria1:="Yes"

So starting from scratch, if I select "Adult and Specialtist Rehabilitation", then column A is correctly filtered. But then if select "Adult Community", it filters it to rows where column A and B both contain "Yes"

So how should I reset in between these lines?

 

[Joe78man]

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8 'removes filter of col8

So basically you set the filter and specify no criteria.
I usually record what I want the macro to do and then use that code for my scripts

 

[bigpat]

Thank you. Yes I usually record macros too (the occasional time I attempt them) but I couldn't work out how to record an IF.

So based on your suggestion I inserted the following seven lines before the previous ones, which seems to have done the trick. I hadn't worked out how to reset the Criteria, but it seems you simply leave that part of the statement off.

'Clear any previous filters first
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=3
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=4
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=5
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=6
ActiveSheet.Range("$A$1:$AG$10001").AutoFilter Field:=7

You live and learn! Thank you.

 

[Fluff]

You could replace all those lines with

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

 

[bigpat]

Thanks. Yes I did play around with that on an earlier attempt but there are a couple of other columns that I hadn't mentioned and I want to retain filters on them.

 

[Fluff]

OK, in that case, if your interested another option would be

   Dim i As Long
   For i = 1 To 7
      ActiveSheet.Range("A1:AG1").AutoFilter i
   Next i