Excel VBA Auto Filter: A Complete Guide With Examples (2023)

Many Excel features are also available for use in VBA, and thatautomatic filteris one of those features.

If you have a set of data and want to filter it using criteria, you can easily do so using the Filter option on the Data ribbon.

And if you want a more advanced version of that, there's aadvanced filteralso in Excel.

So why use AutoFilter in VBA?

If you just need to filter data and do a few basic things, I recommend using the built-in filtering feature that Excel's interface offers.

You should use VBA's automatic filter if you want to filter data as part of your automation (or if it helps save time by making data filtering faster).

Suppose you want to quickly filter data based on a dropdown selection, and then copy the filtered data to a new worksheet.

While this can be done using the built-in filter feature along with copy and paste, it can be time consuming to do manually.

In this scenario, using the VBA Autofilter can speed things up and save time.

observation: I'll cover this example (of filtering data based on a dropdown selection and copying it to a new worksheet) later in this tutorial.

Excel-VBA-AutoFilter-Syntax

Expression. Autofilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Expression: This is the area to which you want to apply the automatic filter.
• campo:[optional argument]This is the column number you want to filter by. This is counted from the left in the data record. So if you want to filter the data based on the second column, this value would be 2.
• criterion 1:[optional argument]This is the criteria by which you want to filter the record.
• Operator:[optional argument]If you also use criteria 2, you can combine these two criteria depending on the operator. The following operators are available:xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• criterio2:[optional argument]This is the second criteria by which you can filter the record.
• dropdown menu visible:[optional argument]You can specify whether or not to display the filter dropdown on filtered columns. This argument can be TRUE or FALSE.

Except for Expression, all other arguments are optional.

If you don't use an argument, it will simply apply or remove the filter symbols from the columns.

Sub FilterRows() Worksheets("Filtrar datos").Rango("A1").AutoFilterEnd Sub

The above code would simply apply the autofilter method to the columns (or, if it's already applied, remove it).

This simply means that if you can't see the filter icons in the column headers, you'll see them when the above code runs, and if you can see them, they'll be removed.

If you have filtered data, the filters will be removed and you will see the full data set.

Now let's look at some examples of how to use Excel VBA Autofilter that will make the usage clearer.

Example: Filtering data based on a text condition

Suppose you have a data set as shown below and you want to filter it based on the Item column.

The following code would filter all lines where the element is "printer".

Sub FilterRows()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"End Sub

The above code refers to Sheet1 and in it to A1 (a cell in the dataset).

Note that we use Field:=2 here because the item column is the second column from the left in our data set.

But before I show you these, let me cover a few examples to show you what the AutoFilter method can do.

Click hereto download the sample file and participate.

Example: multiple criteria (AND/OR) in the same column

Suppose I have the same dataset, and this time I want to filter all datasets where the item is "Printer" or "Projector".

The following code would do this:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Impresora", Operador:=xlOr, Criteria2:="Proyector"End Sub

Note that I used herexlOROperator.

This tells VBA to use both criteria and filter the data if one of the criteria is met.

Similarly, you can also use AND criteria.

For example, if you want to filter all records where the number is greater than 10 but less than 20, you can use the following code:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20" End Sub

Example: multiple criteria with different columns

Suppose you have the following data set.

The automatic filter allows you to filter multiple columns at once.

For example, if you want to filter all records where item is "Printer" and seller is "Brand", you can use the following code:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Example: Filter the top 10 records using the AutoFilter method

Suppose you have the following data set.

Below is the code that gives you the top 10 records (based on the quantity column):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operador:=xlTop10ItemsEnd Sub

In the above code I used ActiveSheet. You can use the name of the worksheet if you want.

Note that in this example you simply need to change the number if you want to get the top 5 itemsCriterio1:="10"from 10 to 5.

So for the top 5 items the code would be:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operador:=xlTop10ItemsEnd Sub

It may sound strange, but no matter how many parent elements you want, the value of the operator is always preserved.xlTop10Item.

Similarly, the following code would provide the bottom 10 items:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operador:=xlBottom10ItemsEnd Sub

And if you want the bottom 5 items, change the numberCriterio1:="10"from 10 to 5.

Example: Filter out the top 10% using the AutoFilter method

Assuming you have the same dataset (used in the previous examples).

Here is the code that returns the top 10 percent records (based on the quantity column):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operador:=xlTop10PercentEnd Sub

Since we have 20 records in our dataset, the first 2 records (which is 10% of the total records) will be returned.

Example: Use of wildcards in the automatic filter

Suppose you have a data set as shown below:

If you want to filter all lines where the element name contains the word "Board", you can use the following code:

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

In the above code I used thewildcards* (asterisk) before and after the word "board" (this is the criteria).

An asterisk can represent any number of characters. This would filter out all items that contain the word "board".

Example: copy filtered rows to a new worksheet

If you not only want to filter the records by criteria, but also want to copy the filtered rows, you can use the following macro.

Copy the filtered rows, add a new worksheet, and paste those copied rows into the new worksheet.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "No hay filas filtradas" Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng. Copiar área ("A1") End Sub

The above code would check if there are any filtered rows on Sheet1 or not.

If there are no filtered rows, this is indicated in a message box.

And if there are leaked lines, they will be copied,insert new worksheetand paste these rows into the newly pasted worksheet.

Example: Filter data based on a cell value

Using AutoFilter in VBA together with athe drop down list, you can create a function where after selecting an item from the dropdown list, all records of that item will be filtered.

Something as shown below:

Click hereto download the sample file and participate.

This type of construct can be useful when you want to quickly filter data and use it in your work.

Below is the code that will do this:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B$2" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field :=2, Criterio1:=Intervalo("B2") End IfEnd IfEnd Sub

that's aWorksheet Event Codewhich is only executed when there is a change in the worksheet and the destination cell is B2 (where we have the dropdown).

also aif then otherwiseThe condition is used to check if the user has selected "Everyone" in the dropdown list. If All is selected, the entire record will be displayed.

This code is NOT placed in a module.

Instead, it should be placed at the back of the worksheet that contains that data.

Here are the steps to paste this code into the code window of the spreadsheet:

1. Open the VB editor (keyboard shortcut: ALT + F11).
2. In the Project Explorer panel, double-click the name of the worksheet on which you want this filtering feature.
3. In the worksheet code window, copy and paste the above code.
4. Close the VB editor.

Now when you use the dropdown the data will be filtered automatically.

that's aWorksheet Event Codewhich is only executed when there is a change in the worksheet and the destination cell is B2 (where we have the dropdown).

also aif then otherwiseThe condition is used to check if the user has selected "Everyone" in the dropdown list. If All is selected, the entire record will be displayed.

Enable/disable Excel autofilter with VBA

When you apply the automatic filter to a range of cells, some filters may already be in effect.

You can use the following code to disable all pre-applied Live Filters:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

This code scans the entire sheets and removes any applied filters.

If you don't want to disable filters for the whole worksheet, but only for a specific record, use the following code:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Rango("A1").AutoFilter Then Worksheets("Sheet1").Rango("A1").AutoFilter End IfEnd Sub

The above code checks if there are already filters or not.

If filters have already been applied, they are removed, otherwise nothing happens.

If you want to enable AutoFilter, use the following code:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd Sub

Check if AutoFilter has already been applied

If you have a worksheet with multiple records and want to ensure that there are no filters, use the following code.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "Filters already exist" ElseMsgBox "Filters do not exist" End IfEnd Sub

This code uses amessage boxFunction that displays the message "Filters already exist" if it finds filters in the worksheet, otherwise displays "No filters."

show all data

If you applied filters to the dataset and want to display all the data, use the following code:

Sub ShowAllData() If ActiveSheet.FilterMode Entonces ActiveSheet.ShowAllDataEnd Sub

The above code checks if FilterMode is TRUE or FALSE.

If true, then a filter has been applied and the ShowAllData method will be used to display all data.

Note that this does not remove the filters. Filter symbols can still be used.

Using AutoFilter on protected sheets

By default, if youprotect a sheetThe filters don't work.

If you already have filters set up, you can enable AutoFilter to make sure it works on protected worksheets as well.

To do this, enable the Use automatic filter when protecting the worksheet option.

While this works if you already have filters set up, if you try to add automatic filters with VBA code, it won't work.

Since the worksheet is protected, it would not allow macros to run or auto filter changes to take place.

So you need to use some code to protect the spreadsheet and make sure you have Live Filters enabled.

This can be useful if you've created a dynamic filter (something I covered in the example: "Filter data based on a cell value").

Below is the code that protects the worksheet, but at the same time allows the use of filters and VBA macros on it.

Private child workbook_Open() with worksheets ("Sheet 1"). EnableAutoFilter = True.Protect Password:="password", Content: = True, UserInterfaceOnly: = TrueEnd WithEnd Sub

This code should be pasted into the ThisWorkbook code window.

Here are the steps to paste the code into the ThisWorkbook code window:

1. Open the VB editor (keyboard shortcut: ALT + F11).
2. In the Project Explorer panel, double-click the ThisWorkbook object.
3. Copy and paste the above code into the code window that opens.

After opening the workbook and enabling macros, this will be the caserun a macroautomatically and protects Sheet1.

Before that, however, "EnableAutoFilter=True" is specified, which means that the filters will also work on the protected spreadsheet.

Also, the UserInterfaceOnly argument is set to true. This means that the VBA macro code will continue to work as long as the worksheet is protected.

You may also like the following VBA tutorials:

• VBA loops do Excel.
• Filter cells with bold formatting.
• record a macro.
• Sort data with VBA.
• Sort sheet tabs in Excel.