How to use VBA Excel AutoFilter

Introduction

The VBA Excel Autofilter let’s us filter a range of cells in our worksheets with a desired order. Sorting is necessity in worksheets and we’ll show you how to do it in this tutorial.

The VBA Excel Autofilter

Let’s first take a look at some sample data:

By using the code below, we can automatically put a filter on every column in the worksheet.

Code:

Sub example1()  

Sheet1.Range("A1:D1").AutoFilter  

End Sub

Code explanation:

- sub-procedure code:

Sub example1() End Sub

- This is the range of cells that we wanted to be filtered.

Sheet1.Range(“A1:D1”)

- And lastly, this is the function that we wanted to create.

.AutoFilter

Now let’s take a look at the result after running the code:

Auto filtering with criteria

Code:

Sub example1()  

Sheet1.Range("A1:D1").AutoFilter field:=4, Criteria1:=1  

End Sub

Adding to the above code:

- We need to add the column number we wish to filter

field:=4

- We also need to add the criteria of the filter. Here we will look for filter for values with a value equal to one.

Criteria1:=1

Let’s take a look at the result:

Auto filtering with two or more criteria:

Code:

Sub example1()  

Sheet1.Range("A1:D1").AutoFilter field:=4, Criteria1:=Array("1", "8"), Operator:=xlFilterValues  

End Sub

Code explanation:

- `Criteria1:=Array("1", "8")` is used to determine the two values we are looking in.

- `Operator:=xlFilterValues` is used to filter one field with more than one value.

Let’s take a look at the result:

Auto filtering with multiple criteria

Code:

Sub example1()  

Sheet1.Range("A1:D1").AutoFilter field:=4, Criteria1:=">1", Operator:=xlAnd, Criteria2:="<8"  

End Sub

Code explanation:

- Criteria1:=">1", Operator:=xlAnd, Criteria2:="<8"

This refers to the values of criteria we are looking for: The first criteria state the values that are greater than 2. The second criteria state the values that are less than 8.

• Operator:=xlAnd is used to filter one field with more than one values and this includes multiple criteria.

Let’s take a look at the result:

We have seen some parameters which we have used when creating auto filtering. Let’s see more of the common parameters and its definition:

- ***`xlOr`*** - this will let you include one of the multiple criteria – Criteria1 or Criteria 2 on a range of cells.

- ***`xlTop10Items`*** - this will let you filter certain values on the highest-ranked (number specified in Criteria1) on a range of cells.

- ***`xlBottom10Items`*** - this will let you filter certain values on the lowest-ranked (number specified in Criteria1) on a range of cells.

- ***`xlTop10Percent`*** - this will let you filter certain percentage values on highest ranked (% specified in Criteria1) on a range of cells.

- ***`xlBottom10Percent`*** - this will let you filter certain percentage values on lowest-ranked (% specified in Criteria1) on a range of cells.

- ***`xlFilterValues`*** - this will include multiple criteria values with Arrays on a range of cells.

- *** `xlFilterCellColor`*** - this will let you filter cells for colors on a range of cells.

- ***`xlFilterFontColor`*** - this will let you filter cells for font colors on a range of cells.

- ***`xlFIlterIcon`*** - this will let you filter icons on a range of cells.

- ***`xlFilterDynamic`*** - this will let you filter dynamic values on a range of cells.

Conclusion

In this tutorial we’ve a shown a few examples of using VBA to AutoFilter your data in Excel. We’ve shown examples of filtering by a single criteria as well as multiple criteria. We hope you are able to apply AutoFilter to your worksheets!