VBA to filter Multiple Columns code helps applying the filters in multiple columns. Let us see the example macros to filter the records or rows based on items in multiple columns using VBA.
Example Data to Explain the VBA Filter:
Here is the sample data to explain the macro on VBA Filter Multiple Columns. This Example Data sheet contains 100 records with example records. We have prepared this simple employee records with multiple columns: Country, Department, DOJ, Salary, Serial Number, Name to clearly explain this topic.
VBA to Filter Multiple Columns:
VBA Macro to filter data with Multiple Columns code applies the Excel filter on multiple fields. We have 6 different Fields in the above data set and we will filter the data using two columns. Let us understand the scenario.
In the data we have County and Department Fields, if you want to see all records if Country = US and Department =IT, then we need to apply the filter on multiple columns.
So, we have to apply our first filter on Column 3 and the Second filter on Column 4. Here is the VBA Macro to filter data with multiple columns.
Sub sbAT_VBA_Macro_To_FilterMultipleColumn() ' VBA Code to filter records of Columns A to F based on the data item in Multiple Columns (Column C and D) With ActiveSheet.Range("$A$1:$F$101") .AutoFilter Field:=3, Criteria1:="US" .AutoFilter Field:=4, Criteria1:="IT" End With End Sub
VBA to Apply Filter on the First Column: In this example Macro, we have applied the filter on Column 3 (i.e; Column C) and the we set the filter criteria is equals to specific country. I this example we have filtered US rows.
ActiveSheet.Range("$A$1:$F$101") .AutoFilter Field:=3, Criteria1:="US"
The above statement will filter the all records in the range A1:A101 which Country equals to US.
VBA to Apply Filter on the Second Column: In this example Macro, we have applied the filter on the second Column 4 (i.e; Column D) and the we set the filter criteria is equals to specific department. I this example we have filtered IT rows.
ActiveSheet.Range("$A$1:$F$101") .AutoFilter Field:=4, Criteria1:="IT"
The above statement will apply the second filter on top of the first filter and filter the records of IT.
After executing this macro, you can find that the row are filter based on Filters applied on Multiple Columns. Out of 100 records, we got 5 records after applying the filter on multiple columns using VBA.
Download the Example File for Filtering Multiple Columns using VBA :
Here is the Example file with sample data. You can download the Excel VBA Macro file and Explore to see the VBA code to filter multiple columns.
Executing the Macro: VBA Filter Column:
Open the Example File with VBA code for Filtering Data on multiple columns
Go to Data sheet, you can observe that there are 100 records.
Open VBA Editor (Press Alt+F11 to open it)
And Run the Macro to Filter Multiple Column by pressing F5 Key.
Now you can see the filtered records in Active sheet.