I'm trying to filter my data using 2 columns and 2 different filter requirements.
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$K$2:$ZZ$200").AutoFilter Field:=5, Criteria1:="yes"
ActiveSheet.Range("E:E").AutoFilter Field:=1, Criteria1:="Low"
If I filter by yes then it work and if I filter by Low it also works. However if I try and filter range K2-ZZ200 by yes and then Column E by Low it seems to hide everything.
Any ideas?
Executing the Macro: VBA Filter Column: Go to Data sheet, you can observe that there are 100 records. And Run the Macro to Filter Multiple Column by pressing F5 Key. Now you can see the filtered records in Active sheet. 120+ Professional Project Management Templates!
If you have a table with multiple columns in Excel, you can filter the data by multiple columns at once. Say you have the data table shown below.
Use a "helper column" with a formula in column B and then filter on that - e.g. =ISNUMBER(A2) or =NOT(A2="A", A2="B", A2="C") then filter on TRUE.
To filter more fields or columns simply copy and paste this line in the macro (Range("A1"). AutoFilter Field:=2, Criteria1:="Enter Criteria Here" ) and change the field number and the criteria. That is all you have to do to filter more than two columns at once.
You have to use the same Range("$K$2:$ZZ$200")
for both AutoFilter
statements:
' Column A = Field 1
ActiveSheet.Range("$K$2:$ZZ$200").AutoFilter Field:=1, Criteria1:="yes"
' Column E = Field 5
ActiveSheet.Range("$K$2:$ZZ$200").AutoFilter Field:=5, Criteria1:="Low"
Also, you may need to set ActiveSheet.AutoFilterMode = True
, but I'm not entirely sure.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With