VBA Filter

Filter in Excel VBA

VBA Filter 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

We all work with filters day in day out, it is almost an integral part of our daily usage of excel. The same feature is also available with VBA as well. The moment we say VBA doesn’t get panic in this article we will explain to you the full details about Excel “VBA Filter”.

In VBA AutoFilter is a function which includes many syntax values. Below are the parameters involved in the “AutoFilter” function in VBA.

• 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”).
• Field is the first argument in the AutoFilter function. Once the range of cells is selected through VBA RANGE object we need a 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.
• Operator is used in case if you want to use 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 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

Example #1 – Apply or Remove Filter to the Data

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

To activate 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 RANGE object.

Code:

Sub Filter_Example()

  Range ("A1:G31")

End Sub

Now access the AutoFilter function for this range.

Code:

Sub Filter_Example()

  Range("A1:G31").AutoFilter

End Sub

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

This code works as a toggle, 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 parameters of the AutoFilter option. Take the same data for an example, now we need to filter out all “Male” gender names.

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.

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

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

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.

First supply the Range of cells and Field.

Code:

Sub Filter_Example()

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

End Sub

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

Code:

Sub Filter_Example()

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

End Sub

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

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

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

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.

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.

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 “WITH” statement.

Code:

Sub Filter_Example()

  With Range("A1:G31")

  End With

End Sub

Now inside WITH statement supply first criteria to be filtered.

Code:

Sub Filter_Example()

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

End Sub

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

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

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 and delete filter to excel vba data along with some examples and download excel template. Below are some useful excel articles related to VBA –

• Use VBA Do Loop in Excel
• How to Use VBA LOOKUP in Excel?
• VBA CDate | Type Conversion Function
• How to Enable Solver in VBA?
• AutoFilter in VBA
• How to Filter in Excel?
• Filter in Pivot Table
• How to Add Filter in Excel?