WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Filter Data

VBA Filter Data

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA Filter

VBA Filter tool is used to sort out or to fetch the specific data desired, Autofilter function is used as an worksheet function, however this function has other arguments with it which are optional and the only mandatory argument is the expression which covers the range for example worksheets(“Sheet1”).Range(“A1”).Autofilter will apply the filter on first column.

Filter in VBA works the same way it works in the worksheet. The only thing different is we can automate the routine task of filtering the data through coding.

VBA Filter

AutoFilter is a function that includes many syntax values. Below are the parameters involved in the AutoFilter function.

AutoFilter Syntax

  • The range is the first thing we need to supply to use the “AutoFilter” option. This is simply for which range of cells we need to apply the filter, for example, Range (“A1:D50”).
  • The field is the first argument in the function. Once the range of cells is selected through the VBA RANGE object, we need to mention for which column of the range we want to apply the filter for.
  • Criteria 1 is nothing but in the selected Field, what value you want to filter out.
  • The operator is used in case if you want to use the Criteria 2 argument. In this option, we can use the below options.
    xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlTop10Percent, xlBottom10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
  • Visible Dropdown is to whether to display a filter symbol in the filter applied column or not. If you want to display, then you can supply the argument as TRUE or else FALSE.

Examples to Filter Data using VBA

You can download this VBA Filter Excel Template here – VBA Filter Excel Template

Example #1 – Apply or Remove Filter to the Data

In case if you want to apply the filter option to the data, then we can turn off and on this option. For example, look at the below data image.

Remove Filter to Data 1

Step 1: Supply data range

To activate the filter option first, we need to supply what is our data range. In the above image, our data is spread across from A1 to G31, so supply this range by using a RANGE object.

Code:

Sub Filter_Example()

  Range ("A1:G31")

End Sub

Remove Filter to Data 1-1

Step 2: Then access AutoFilter function

Now access the AutoFilter function for this range.

Code:

Sub Filter_Example()

  Range("A1:G31").AutoFilter

End Sub

Remove Filter to Data 1-2

Step 3: Run the code to enable the filter

That’s all. Run this code to enable the auto filter.

Remove Filter to Data 1-3.

This code works as a toggle, and if the filter is not applied, then it will apply. If already applied, then it will remove.

Example #2 – Filter Specific Values

Now we will see how to use the parameters of the AutoFilter option. Take the same data as above. For example, now we need to filter out all “Male” gender names.

Step 1: Select Range and Open Autofilter Function

Filter Specific Values 1

Step 2: Then Select Field

In the first argument of the function i.e., Field, we need to mention the column reference that we would like to filter out. In this example, we need to filter only “Male” candidates, which is column “C,” so column number is 3.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Filter Specific Values 1-1

Step 3: Now Mention Criteria

Now for this supplied Field, we need to mention the Criteria 1 i.e., what value we need to filter in the mentioned Field. We need to filter “Male” from this column.

Code:

Sub Filter_Example()

  Range("A1:G31").AutoFilter Field:=3, Criteria1:="Male"

End Sub

Filter Specific Values 1-2

Step 4: And run the code

Ok, that’s all. This code will filter only “Male” candidates now.

Filter Specific Values 1-3

Example #3 – Usage of OPERATOR Argument

When you want to filter out more than one value from the column, we need to use the “Operator” argument. For example, from the column “Major,” we need to filter only “Math & Politics,” then we need to use this argument.

Step 1: Select Range and Autofilter Field

First, supply the Range of cells and fields.

Code:

Sub Filter_Example()

 Range("A1:G31").AutoFilter Field:=5,

End Sub

Use of OPERATOR Argument 1

Step 2: Enter Criteria 1 as Math

For the mentioned filed, we need to supply Criteria 1 as “Math.”

Code:

Sub Filter_Example()

 Range("A1:G31").AutoFilter Field:=5, Criteria1:="Math",

End Sub

Use of OPERATOR Argument 1-1

Step 3: Use Operator xl

Since we need to filter one more value from the same column or field, use the operator symbol as “xlOr.”

Code:

Sub Filter_Example()

  Range("A1:G31").AutoFilter Field:=5, Criteria1:="Math", 
  Operator:=xlOr

End Sub

Use of OPERATOR Argument 1-2

Step 4: Enter Criteria 2 as Politics

And for Criteria 2 argument mention the value as “Politics.”

Code:

Sub Filter_Example()

 Range("A1:G31").AutoFilter Field:=5, Criteria1:="Math",
 Operator:=xlOr, Criteria2:="Politics"

End Sub

Use of OPERATOR Argument 1-3

This will filter out both “Math” & “Politics” from column “Major.”

Use Row Function Example 1-6

Example #4 – Filter Numbers with Operator Symbols

For example, if you want to filter numbers with, then we can filter a specific number as well as numbers above, below, or between specific values and range of values.

For example, from the age column, if you want to filter persons aged more than 30, then we can write the code like below.

Code:

Sub Filter_Example()

 Range("A1:G31").AutoFilter Field:=7, Criteria1:=">30"

End Sub

This will filter all the values that are more than 30.

Excel VBA Filter Numbers 1

Now, if you want to filter values between 21 and 31, then we can use the below code.

Code:

Sub Filter_Example()

 Range("A1:G31").AutoFilter Field:=7, Criteria1:=">21", 
 Operator:=xlAnd, Criteria2:="<31"

End Sub

This will filter persons aged between 21 and 30.

Excel VBA Filter Numbers 1-1

Example #5 – Apply Filter for More Than One Column

If you want to filter values from more than one column criteria, then we need to use a slightly different technique.

If you want to filter “Student Status” as “Graduate” and “Country” as “US,” then first, we need to supply the RANGE of cells under the “WITH” statement.

Code:

Sub Filter_Example()

  With Range("A1:G31")

  End With

End Sub

Example 5

Now inside WITH statement, supply the first criteria to be filtered.

Code:

Sub Filter_Example()

 With Range("A1:G31")
  .AutoFilter Field:=4, Criteria1:="Graduate"
 End With

End Sub

Example 5-1

Now in the next line, do the same for “Country” by changing “Field” as 6 and Criteria as “US.”

Code:

Sub Filter_Example()

 With Range("A1:G31")
  .AutoFilter Field:=4, Criteria1:="Graduate"
  .AutoFilter Field:=6, Criteria1:="US"
 End With

End Sub

Example 5-2

Now this will filter “Graduate” only for the country “US.”

Example 5-3

Things to Remember

  • The first thing first only for the mentioned range of cells filter will be applied.
  • The field is nothing in which column you want to filter the data.
  • In case filtering values from more than one column, then use With.

Recommended Articles

This has been a guide to VBA Filter. Here we learn how to apply a filter to data along with some VBA examples and download an excel template. Below are some useful excel articles related to VBA –

  • VBA AutoFilter
  • VBA Do Loop
  • How to Enable Solver in VBA?
  • Add Filter in Excel
  • VBA Paste Values
5 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Filter Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More