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.

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.

• 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)

Assume below the data you are workings on.

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.

Code:

Sub AutoFilter_Example1()

End Sub

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

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

Code:

Sub AutoFilter_Example1()

  Range("A1:E25").AutoFilter

End Sub

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.

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

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

Code:

Sub AutoFilter_Example1()

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

End Sub

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

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

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

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

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

Step 2: Criteria 1 is >1000.

Code:

Sub AutoFilter_Example3()

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

End Sub

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

Step 4: Criteria 2 will <3000.

Code:

Sub AutoFilter_Example3()

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

End Sub

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 #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.

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

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