Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple Filtering in Excel VBA?

Tags:

excel

vba

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?

like image 599
K20GH Avatar asked Aug 20 '12 15:08

K20GH


People also ask

How do I apply multiple filters in Excel VBA?

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!

Can you use multiple filters at once in Excel?

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.

How we can filter the multiple criteria in VBA code?

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.

How do I filter multiple columns in Excel VBA?

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.


1 Answers

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.

like image 62
Ryan Avatar answered Oct 01 '22 07:10

Ryan