VBA AutoFilter

AutoFilter in VBA is which we can use as an expression. The syntax for it is as follows: Expression. Autofilter(Field, Criteria 1, Operator, Criteria 2, Dropdown) all of the arguments are optional. The filter helps filter the particular data from the huge data.

Suppose you are a regular user, then excel filters are not a strange thing for you. Also, some of the tools we use in the worksheet are integrated into VBA, and the AutoFilter option is one of the important tools available in VBA. As we all know, the 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 think the VBA AutoFilter option is not required, you can continue using the worksheet filter option.

If you deal with a huge amount of data, you can use an AutoFilter option to simplify the process flow.

We can use the AutoFilter in Excel VBA with a range object. Like our other functions, this, too, has its syntax.

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

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

Criteria 1: You have selected what you want to filter in the field.

• 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. The argument is if you want to display TRUE, if not FALSE.

Assume below the data you are working 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 the range we need to apply the filter. In this case, we must apply the range from A1 to E25.

Code:

Sub AutoFilter_Example1()

  Range ("A1:E25")

End Sub

Step 3: Apply the AutoFilter option once you select the range.

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.

Run the code using the F5 key or manually. It will insert a 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. Again, we must filter out the "Finance" department from the "Department" column.

Step 1: After applying the filter, 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 the column number is 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

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. For example, assume along with "Finance" you want to filter the "Sales" department, 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: 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: Run this code using the F5 key or manually. It will filter out both "Finance" and "Sales."

In the operator argument, we have used "xlOr," which will select both "Finance" and "Sales" under the filter.

Example #3 - Filter Numbers using AutoFilter

Using the operator symbol "XlAnd," we can also filter out numbers. For example, 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

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

We need to use a different process to filter data from different columns. For example, assume you want to filter out the "Finance" department. Also, you want to filter out salary numbers that are >25000 but <40000.

You can use the below code to do 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

It will filter out two columns.

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

• We must try different combinations under the 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, you must supply them in double quotes.
• We must use a WITH statement to apply more than one column filter.