WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA AutoFilter

VBA AutoFilter

Excel VBA AutoFilter

AutoFilter in VBA is used as an expression and the syntax for it as follows Expression. Autofilter(Field, Criteria 1, Operator, Criteria 2, Dropdown) all of the arguments are optional. The filter is helpful in filtering the particular data from the huge data.

Suppose you are a regular user, then excel filters are not a strange thing for you. Some of the tools we use in the worksheet are also integrated into VBA as well, and the auto filter option is one of the important tools available in VBA. As we all know, excel filter is available under the data tab.

Data Filter

Using this filter option, we can play around with the data. If you are dealing with a minimal amount of data and if you think VBA autofilter option is not really required, you can continue to use the worksheet filter option.

If you are dealing with a huge amount of data, then you can use an Autofilter option to simplify the process flow.

Syntax of AutoFilter Function

AutoFilter in Excel VBA can be used with a range object. Like our other functions, this, too, has its own syntax.

VBA AutoFilter Formula

  • Range: Range is simply in what range you would like to apply the filter.
  • Field: Field means from which column of the range you have selected you to want to filter out the data. A column will be counted from left to right.

For example, if your range is from A1 to D100 and you want to apply a filter for D column, then your field number is 4 because, in the selected range, D is the fourth column.

Criteria 1: In the field, you have selected what you want to filter.

  • Operator: If you want to apply more than one criterion for filtering the data, then we need to use operator symbols. Some of the operator codes are as below.

xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlTop10Percent, xlBottom10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues.

Criteria 2: What is the second thing you want to filter along with Criteria 1?

  • Visible Drop Down: Do you want to display the icon of filter fop down in the filtered column. If you want to display TRUE is the argument, if not FALSE, is the argument.

How to Apply Filter using VBA AutoFilter? (with Example)

You can download this VBA AutoFilter Template here – VBA AutoFilter Template

Assume below the data you are workings on.

VBA Autofilter Example 1

Now by using VBA code, we will apply the filter. Follow the below steps to apply the filter.

Step 1: Start the subprocedure by giving a name to the macro.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub AutoFilter_Example1()

End Sub

VBA Autofilter Example 1-1

Step 2: The first thing is in what range we need to apply the filter. In this case, we need to apply the range from range A1 to E25.

Code:

Sub AutoFilter_Example1()

  Range ("A1:E25")

End Sub

VBA Autofilter Example 1-2

Step 3: Once the range has been selected, now apply the auto filter option.

Code:

Sub AutoFilter_Example1()

  Range("A1:E25").AutoFilter

End Sub

VBA Autofilter Example 1-3

We are not selecting any values using an autofilter. Rather, we are just applying the filter at this point in time.

Run the code using the F5 key, or manually, it will insert filter for the selected range.

VBA Autofilter Example 1-4

So it has applied a filter to the data. Now we will see how to filter out the data.

Example #1 – Filter Out Data by Using AutoFilter

Consider the same data from the above example. Now we need to filter out the “Finance” department from the department column.

Step 1: After the filter has been applied, the first argument is to mention from which column we are filtering the data. In this case, we need to filter the data from the Department column, so column number if 5.

Code:

Sub AutoFilter_Example1()

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

End Sub

VBA Autofilter Example 1-5

Step 2: Criteria 1 is nothing but what we want to filter from the 5th column. So we need to filter out “Finance.”

Code:

Sub AutoFilter_Example1()

  Range("A1:E25").AutoFilter Field:=5, Criteria1:="Finance"

End Sub

VBA Autofilter Example 1-6

So that’s all, run this code manually, or through the F5 key, it will filter out only “Finance” from the list.

VBA Autofilter Example 1-7

Example #2 – Operator in AutoFilter

We have seen how to filter the single value. Now we will see how to filter multiple elements. Assume along with “Finance” you want to filter the “Sales” department as well, so we can do this by using Operators.

Step 1: After applying the first criteria in the next argument, select the operator as “xlOr.”

Code:

Sub AutoFilter_Example2()

  Range("A1:E25").AutoFilter Field:=5, Criteria1:="Finance", Operator:=xlOr

End Sub

VBA Autofilter Example 2

Step 2: Now, in Criteria 2, mention the value as “Sales.”

Code:

Sub AutoFilter_Example2()

Range("A1:E25").AutoFilter Field:=5, Criteria1:="Finance", Operator:=xlOr, Criteria2:="Sales"

End Sub

VBA Autofilter Example 2-1

Step 3: Ok, done run this code using the F5 key or manually, it will filter out both “Finance” & “Sales.”

VBA Autofilter Example 2-3

In the Operator argument, I have used “xlOr” this will select both “Finance” & “Sales” under the filter.

Example #3 – Filter Numbers using AutoFilter

Using the Operator symbol “XlAnd,” we can filter out numbers as well. Assume from the Overtime column you want to filter out all the values which are >1000 but <3000.

Step 1: The first thing is changing the Field from 5 to 4.

Code:

Sub AutoFilter_Example3()

  Range("A1:E25").AutoFilter Field:=4

End Sub

Example 3

Step 2: Criteria 1 is >1000.

Code:

Sub AutoFilter_Example3()

  Range("A1:E25").AutoFilter Field:=4,Criteria1:=">1000",

End Sub

Example 3-1

Step 3: Here, we need to match both the criteria, so use “xlAnd” as the operator.

Code:

Sub AutoFilter_Example3()

Range("A1:E25").AutoFilter Field:=4, Criteria1:=">1000", Operator:=xlAnd,

End Sub

Example 3-2

Step 4: Criteria 2 will <3000.

Code:

Sub AutoFilter_Example3()

Range("A1:E25").AutoFilter Field:=4, Criteria1:=">1000", Operator:=xlAnd, Criteria2:="<3000"

End Sub

Example 3-3

Now, if you run this code manually or through the F5 key, it will filter out all the values from the Overtime column, which are >1000 but <3000.

Example 3-4

Example #4 – Filter from Different Columns using AutoFilter

In order to filter out data from different columns, we need to use a different process. Assume you want to filter out the “Finance” department and also you want to filter out Salary numbers, which are >25000 but <40000.

You can use the below code does that.

Code:

Sub AutoFilter_Example4()

      With Range("A1:E25")
.AutoFilter Field:=5, Criteria1:="Finance"
.AutoFilter Field:=2, Criteria1:=">25000", Operator:=xlAnd, Criteria2:="<40000"
      End With
End Sub

This will filter out two columns.

Example 4

Run this code using the F5 key, or you can run manually.

Example 4-1

Things to Remember

  • Try different combinations under Operator to get the knowledge of VBA AutoFilter.
  • If you are not sure what to put in, try using the macro recorder.
  • If you want to filter out text values, then you need to supply them in double-quotes.
  • Use WITH statement to apply more than one column filter.

Recommended Articles

This has been a guide to VBA AutoFilter. Here we learn how to apply the filter using VBA autofilter, and we also learn how to filter out data using different excel VBA autofilter criteria such as Operators, Numbers, and Filter from different columns along with some simple to advanced examples. Below are some useful excel articles related to VBA –

  • Add Filter in Excel
  • Excel VBA TRIM Function
  • VBA Range
  • UsedRange in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ 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 AutoFilter Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More