VBA Filter Multiple Columns

Home/VBA/VBA Filter/VBA Filter Multiple Columns

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


Example Data to Explain the VBA Filter:

VBA Filter Multiple Columns Example Data

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.

VBA Filter Multiple Columns Filtered Data

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.

VBA Filter Multiple Columns Macro File

 

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.

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference
By |August 11th, 2018|VBA Filter|2 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

2 Comments

  1. Mike August 22, 2018 at 11:47 AM - Reply

    Thanks for the macro, it is very helpful for filtering the data in multiple columns.

  2. Pooja October 5, 2018 at 11:18 PM - Reply

    Dear Sir, Thank you so much for the work .. It helped me not only implement quick, but to learn well.. The steps description,, demo download are so worth. I could use it to practice with right guidance..

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.