bottom line:Learn how to create macros that apply filters to ranges and tables using the AutoFilter method in VBA. The post contains links to examples of filtering different types of data, including text, numbers, dates, colors, and symbols.
Skill Level:intermediary
Download the file
The Excel file with the code can be downloaded below. This file contains code to filter various types of data and filter types.
VBA-AutoFilters-Guide.zipdiscard
Write macros for filters in Excel
Filters are a great tool for analyzing data in Excel. For most analysts and frequent Excel users, filters are part of everyday life. We'll use the filter dropdown menus to apply filters to individual columns in a dataset. It helps us link numbers to reports and examine our data.
Filtering can also be a slow process. Especially when applying filters to multiple columns in large worksheets or filtering data to copy/paste later into other worksheets or workbooks.
This article explains how to create macros to automate the filtering process. that is aDetailed guide on AutoFilter method in VBA.
I also have articles with examples of different filters and data types, including:spaces,Text,counting,Deadline,, jfilter cleaning.
Macro recorder is your friend (and enemy)
We can easily get the VBA code for the filters by activating the macro recorder and applying one or more filters to a range/table.
Here are the steps to create a filter macro using the macro recorder:
- Turn on the macro recorder:
- Developer Tab > Record Macro.
- Give the macro a name, choose where you want to save the code and press OK.
- Apply one or more filters using the filter drop-down menus.
- Stop the recorder.
- Open the VB editor (Developer tab > Visual Basic) to view the code.
If you've used the macro recorder for this process before, you know how useful it can be. Especially as our filter criteria are becoming more complex.
The code looks like this.
SubFilters_Macro_Recorder()'' Macrofilter_Macro_Recorder''ActiveSheet.ListObjects("tblData").Range.AutoFilter Field:=4, Criteria1:= _"Produto 2"ActiveSheet.ListObjects("tblData").Range.AutoFilter Field:=4ActiveSheet.ListObjects("tblData").Range .Campo AutoFilter:=5, Criteria1:= _">=500", Operador:=xlAnd, Criteria2:="<=1000"Final SubWe can see that each row uses the AutoFilter method to apply the filter to the column. It also contains information about the criteria for the filter.
this and whereit can get complex, confusing, and frustrating.It can be difficult to understand what the code means when trying to change it for a different scenario or dataset. So let's take a look at how the AutoFilter method works.
Explanation of the automatic filter method
The AutoFilter method is used to clean and apply filters to aa single columnon a range or table in VBA. It automates the process of applying filters through the filter dropdowns and does all that work for us. 🇧🇷
Can be used to apply filtersmultiple columns writing multiple lines of code, one for each column. We can also use AutoFilter to applymultiple filter criteria in a single column, just as you would in the filter dropdown by checking multiple boxes or specifying a date range.
Write the auto filter code
Here are step by step instructions on how to write one line of code for AutoFilter
Step 1: Reference the range or table
The AutoFilter method is a member of the Range object. Therefore, we need to refer to a range or table to which the worksheet filters will be applied. This is the entire area to which the filters will be applied.
The following examples enable/disable filters in the range B3:G1000 on the AutoFilter Tab sheet.
SubAutoFilter_Rank()'AutoFilter is a member of the Range object“Refers to the entire area to which the filters apply'AutoFilter enables/disables filters if no parameter is specified.Sheet1.Range("B3:G1000").AutoFilter'Workbook level fully qualified referenceThisWorkbook.Worksheets("Guia do AutoFilter").Range("B3:G1000").AutoFilterFinal SubHere's an example using Excel spreadsheets.
Subtable_autofilter()'Autofilters for tables work the same way.DarkHelloAslist objectexcel table'Define variable ListObject(table).Foundlo = Sheet1.ObjectList(1)'AutoFilter is a member of the Range object'The parent of the Range object is the List objectlo.Range.AutoFilterFinal SubThe AutoFilter method has 5 optional parameters, which we'll cover next.If we don't specify any of the parameters, as in the previous examples, the AutoFilter method activates/deactivates the filters for the referenced range.It toggles when filters are on, when they are off, and vice versa.
Rankings or Tables?
Filters work the same in bothControl strips and Excel worksheets.
My preferred method is to use tables because we don't have to worry about changing range references as the table grows or shrinks. However, the code is the same for both objects. The rest of the code examples use Excel spreadsheets, but you can easily change this to normal ranges.
The 5 (or 6) autofilter parameters
The AutoFilter method has 5 (or 6) optional parameters that specify filter criteria for a column. Here is a list of parameters.
| Name | Mandatory/Optional | Description |
|---|---|---|
| campo | Optional | The column number within the scope of the filter to which the filter is applied. This is the column number within the filter area, NOT the table column number. |
| Criterion 1 | Optional | A quoted string that specifies the filter criteria. Comparison operators can be added for less than or greater than filters. Depending on the column's data type, many rules apply. Check out the examples to continue. |
| Operator | Optional | Specifies the type of filter for various data types and criteria using one of the XlAutoFilterOperator constants. Look at thisMSDN help page for a detailed listand list in the following macro examples. |
| criteria2 | Optional | It is used in combination with the operator parameter and Criteria1 to create filters on multiple criteria or ranges. Also used for certain multi-item date filters. |
| Visible dropdown list | Optional | Shows or hides the filter dropdown button for a single column (field). |
| subfield | Optional | I'm not sure yet… |
We can use a combination of these parameters to apply different filter criteria to different types of data. The first four are the most important, so let's see how to use them.
Step 2: The Field Parameter
The first parameter is the field. For the field parameter we provide a number which is the column number to which the filter will be applied.This is the column number inside the filter areathis is the parent of the AutoFilter method. NOT the column number in the worksheet.
In the example below, Field 4 is the Product column because it is the fourth column in the Scope/Filter table.
Column filter will be cleared if we provide only Field parameter, and no other criteria.
We can also use a variable for the Field parameter and set it dynamically. I will explain to you in more detailLow.
Step 3: The Criteria Parameters
There are two parameters that can be used to specify the criteria filter, criteria1 and criteria2. We use a combination of these parameters and the operator parameter for different filter types. This is where it gets tricky, so let's start with a simple example.
'Filter the product column by a single itemlo.Range.AutoFilter Field:=4, Criteria1:="Product 2"This would be the same as selecting a single item from the list of checkboxes in the filter drop-down menu.
General rules for Criterion 1 and Criterion 2
The values ​​we provide for Criteria1 and Criteria2 can be tricky. The following are some general guidelines for referencing criteria parameter values.
- The criteria value is astring between blades🇧🇷 There are some exceptions where the date range criterion is constant and above or below average.
- By specifying filters for individual numbers or dates thatThe number format must matchthe number format applied to the range/table.
- Losthe greater/less than comparison operator, also enclosed in quotes, before the number.
- Quotation marks are also used to match spaces "=" and non-spaces "<>".
"Filter by date greater than or equal to January 1, 2015lo.Range.AutoFilter Field:=1, Criteria1:=">=1/1/2015"' The comparison operator >= is enclosed in quotes' for parameter criterion1.' The date format in the code matches the format' applied to the worksheet cells.Step 4: The operator parameter
What if we want to select multiple items from the filter drop-down list? Or create a filter for a range of dates or numbers?
For that, we need the operator. The operator parameter is used to specify what kind of filter we want to apply. This can vary depending on the type of data in the column. One of the following 11 constants must be used for the operator.
| Name | bravura | Description |
|---|---|---|
| xlY | 1 | Include criterion 1 and criterion 2. Can be used for date or number ranges. |
| xlBottom10Items | 4 | Items displayed with the lowest value (number of items specified in criterion 1). |
| xlLower10 percent | 6 | Items with the lowest value are displayed (percentage specified in criterion 1). |
| xlFilterCellColor | 8 | cell fill color |
| xlDynamicFilterName | 11 | Dynamic filter used for above or below average periods and data |
| xlFiltroFuenteColor | 9 | Font color in the cell. |
| xl-Filtersymbol | 10 | Filter icon created by conditional formatting |
| xlFilterValues | 7 | Used for filters with multiple criteria specified with an array function. |
| xlO | 2 | Include criterion 1 or criterion 2. It can be used for date and number ranges. |
| xlTop10Artikel | 3 | Articles with the highest displayed value (number of articles specified in criterion 1). |
| xlTop10 percent | 5 | Items with the highest displayed value (percentage defined in Criterion 1). |
Here is a link to the MSDN help page which contains the list of constants forXlAutoFilterOperator Enumeration.
Depending on the data type and filter type, the operator is used in combination with Criteria1 and/or Criteria2. Here are some examples.
'Filter for multi-item list, operator is xlFilterValueslo.Range.AutoFilter _Field:=iCol, _Criteria1:=Array("Product 4", "Product 5", "Product 6"), _Operator:=xlFilterValues'Filter by date range (between dates), operator is xlAndlo.Range.AutoFilter _Field:=iCol, _Criteria1:=">=1.1.2014", _Operator:=xlAnd, _Criteria2:="<=31.12.2015"That's the basics of writing a line of code for the AutoFilter method. It gets more complex with different types of data. Therefore, I have provided many examples below that include most criteria and operator combinations for different filter types.
The automatic filter is NOT additive
When a line of AutoFilter code is executed,first clears the filters applied to that column (field) and then applies the filter criteria specified in the line of code.
This means that it is NOT additive. The next 2 lines do NOT create a filter for product 1 and product 2. After running the macro, the product column only filters product 2.
'AutoFilter is NOT additive. Filters are applied first' in the column before applying the new filterlo.Range.AutoFilter Field:=4, Criteria1:="Product 3""This line of code filters only column Product 2'The above filter for product 3 will be cleared when this line is executed.lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"If you want to apply a multi-criteria filter to a single column, you can specify it using the criteria and operator parameters.
How to set field number dynamically
When adding/removing/moving columns in the filter pane, the field number for a filtered column may change. So I try not to hard-code a number into the field parameter.
Instead, we can use a variable and use some code to find the column number by name. Here are two examples of regular ranges and tables.
Subdynamic_field_number()'Techniques for locating and setting the field based on the column name.DarkHelloAslist objectDarkiColAs Largo'Set reference to the first table in the sheetFoundlo = Sheet1.ObjectList(1)'Define filter fieldiCol = lo.ListColumns("Product").Index'Use the match function for regular intervals'iCol = WorksheetFunction.Match("Produto", Sheet1.Range("B3:G3"), 0)'Use variable for field parameter valuelo.Range.AutoFilter Field:=iCol, Criteria1:="Produto 3"Final SubThe column number will be found every time we run the macro. We don't have to worry about the field number changing when the column is moved. This saves time and avoids mistakes (win-win)! 🇧🇷
Use Excel spreadsheets with filters
Using Excel spreadsheets has many advantages, especially with the AutoFilter method. These are some of the main reasons I prefer spreadsheets.
- We don't need to redefine the range in VBA because the data range will be resized (rows/columns will be added/removed). The entire table is referenced with the ListObject object.
- It's easy to reference the data in the table after applying the filters. We can use the DataBodyRange property to reference visible rows for copy/paste, formatting, changing values, etc.
- We can have multiple tables in the same worksheet and therefore multiple filter areas. With regular ranges, we can only have one filtered range per worksheet.
- Code to clear all filters on a table is easier to write.
Filters and data types
The options in the filter drop-down menu change depending on the type of data in the column. We have different filters for text, numbers, dates and colors. This creates MANY different combinations of operators and criteria for each filter type.
I made separate posts for each of these filter types. The posts contain explanations and examples of VBA code.
- How to clear filters using VBA
- How to filter text using VBA
- How to filter by numbers using VBA
- How to filter by date using VBA
The file in the download area above contains all these code examples in one place. You can add it to yourpersonal macro bookand use the macros in your projects.
Why is the automatic filter method so complex?
This post was inspired by a question from Chris, a member of the VBA Pro Course. The combinations of criteria and operators can be confusing and complex. Why is this?
Well, filters have evolved over the years. We've seen many new filter types introduced in Excel 2010 and the feature just keeps getting better. However, the parameters of the AutoFilter method have not changed. This is great for backward compatibility, but it also means that new filter types can be incorporated into existing parameters.
Most of the filter code makes sense, but it can be difficult to understand at first. Fortunately, we have the macro recorder to help us with this.
I hope you can use this post and the Excel file as a guide for writing macros for filters. Filter automation can save us and our users a lot of time, especially when using these techniques in a larger data automation project.
Please leave a comment below with any questions or suggestions. Thank you very much! 🇧🇷
FAQs
How do you use AutoFilter criteria in Excel VBA? ›
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.
How do I filter AutoFilter in Excel? ›- Select the data you want to filter.
- Click Data > Filter.
- Click the arrow. ...
- Choose specific values: Uncheck (Select All) to clear all of the check boxes, and then check the boxes for the specific value(s) you want to see.
AutoFilter allows filtering data with a maximum of 2 criteria, and those conditions are specified directly in the Custom AutoFilter dialog box. Using Advanced Filter, you can find rows that meet multiple criteria in multiple columns, and the advanced criteria need to be entered in a separate range on your worksheet.
How do you use filtering in Excel? ›- Select any cell within the range.
- Select Data > Filter.
- Select the column header arrow .
- Select Text Filters or Number Filters, and then select a comparison, like Between.
- Enter the filter criteria and select OK.
- Step 1: Supply data range. To activate the filter option first, we need to supply what is our data range. ...
- Step 2: Then access AutoFilter function. Now, access the AutoFilter function for this range. ...
- Step 3: Run the code to enable the filter. That is all.
In photography, a close-up lens (sometimes referred to as close-up filter or a macro filter) is a simple secondary lens used to enable macro photography without requiring a specialised primary lens. They work like reading glasses, allowing a primary lens to focus more closely.
How do you AutoFilter multiple columns in Excel VBA? ›To filter more fields or columns simply copy and paste this line in the macro (Range("A1"). AutoFilter Field:=2, Criteria1:="Enter Criteria Here" ) and change the field number and the criteria. That is all you have to do to filter more than two columns at once.
How many types of filters are there in Excel? ›In Excel, you can create three kinds of filters: by values, by a format, or by criteria. But each of these filter types is mutually exclusive. For example, you can filter by cell color or by a list of numbers, but not by both. You can filter by icon or by a custom filter, but not by both.
What is AutoFilter in MS Excel write the steps with example? ›Excel AutoFilter is an easy way to turn the values in Excel columns into specific filters based on the cell content. The AutoFilter in Excel enables us to filter our data as we desire in one, two, or more columns at once. Excel AutoFilter allows us to slice and dice our data as per our requirements.
What is filter in MS Excel? ›The FILTER function allows you to filter a range of data based on criteria you define.
Can you use filter function in VBA? ›
The VBA FILTER function is listed under the array category of VBA functions. When you use it in a VBA code, it can return strings from an array of strings based on the string you have specified as a subset.
How do I filter a column in Excel VBA? ›- The Filter Range. A Word on Terminology. The Field Parameter.
- Defining the Filter Criteria. Filter For a String. Filter For Multiple Strings. Filter For Values. Special Filters. Leverage User Input.
- Clearing Filters.
- Select the cell of interest and click Apply Filter by Selected Value.
- Filter by selected value is created.
- Select several cells and click Apply Filter by Selected Value.
- The list is filtered by multiple values.
- Clear all filters in one click.
When you filter data, entire rows are hidden if values in one or more columns don't meet the filtering criteria. You can filter on numeric or text values, or filter by color for cells that have color formatting applied to their background or text.
What is filter and types? ›Electronic filters remove unwanted frequency components from the applied signal, enhance wanted ones, or both. They can be: passive or active. analog or digital. high-pass, low-pass, band-pass, band-stop (band-rejection; notch), or all-pass.
What is the importance of filtering data in Excel? ›In addition to sorting, you may find that adding a filter allows you to better analyze your data. When data is filtered, only rows that meet the filter criteria will display and other rows will be hidden. With filtered data, you can then copy, format, print, etc., your data, without having to sort or move it first.
What is filtering why and how it is done in Excel? ›What is Filter in Excel? The filter in excel helps display relevant data by eliminating the irrelevant entries temporarily from the view. The data is filtered as per the given criteria. The purpose of filtering is to focus on the crucial areas of a dataset.
Why is filtering important in Excel? ›You sort data to quickly organize your data and to find the data that you want. You filter data to display only the rows that meet criteria that you specify and hide rows that you do not want displayed, for one or more columns of data.
Are macro filters any good? ›The main benefit of using a macro filter is that it can turn any of your lenses into a macro lens. Unlike other macro attachments, it's also small and lightweight. Finally, it is one of the cheapest options out there for those who want to get into macro photography.
What is a disadvantage of using a macro filter? ›One of the disadvantages of macro filters is that the images will not be as sharp and detailed as they would be if they were taken with a macro lens. Using a UV filter in the studio is completely unnecessary and pointless, unless it serves to protect the front lens of the lens.
What are filters and why are they so important? ›
A filter is a circuit capable of passing (or amplifying) certain frequencies while attenuating other frequencies. Thus, a filter can extract important frequencies from signals that also contain undesirable or irrelevant frequencies. In the field of electronics, there are many practical applications for filters.
How do I filter data in Excel with multiple criteria? ›- Insert at least three blank rows above the list range that can be used as a criteria range. ...
- To find rows that meet multiple criteria in multiple columns, type all the criteria in the same row of the criteria range. ...
- Click a cell in the list range.
To apply a filter for a cell's value: Right-click a cell that contains the value you want to filter for. Choose Filter > Filter by Selected Cell's Value. The filter will be applied to the column.
How do you AutoFilter based on cell value? ›To apply a filter for a cell's value: Right-click a cell that contains the value you want to filter for. Choose Filter > Filter by Selected Cell's Value. The filter will be applied to the column.
How does filter function work in Excel? ›The FILTER function in Excel is used to filter a range of data based on the criteria that you specify. The function belongs to the category of Dynamic Arrays functions. The result is an array of values that automatically spills into a range of cells, starting from the cell where you enter a formula.
Is VBA useful for data analytics? ›Microsoft Excel VBA allows for automating tasks in Excel and provides a full programming environment for data analysis. Macro record a set of actions so they can be easily executed again.
What are the three main categories of the VBA statements? ›Each statement belongs to one of the following three categories: Declaration statements, which name a variable, constant, or procedure and can also specify a data type. Assignment statements, which assign a value or expression to a variable or constant. Executable statements, which initiate actions.
What are the three main logical operators in VBA? ›Logical operators allow you to evaluate one or more expressions and return a Boolean value ( True or False ). VB.NET supports four logical operators: And , AndAlso , Or , OrElse , Not , and Xor . These operators also double as bitwise operators.
How do I filter unique values in Excel VBA? ›- Range("C:C"). AdvancedFilter xlFilterCopy, , Range("H1:H1"), True.
- Range("A:B"). AdvancedFilter xlFilterCopy, , Range("H1:H1"), True.
- Sub wasOriginalUnique() Dim beforeCount, afterCount As Integer Range("A:A"). ...
- Sub uniquesToColumnH() Range("A:A").
- Clear the filters on your sheet or Table.
- Turn the macro recorder on (Developer Tab > Record Macro button)
- Give the macro a name.
- Choose to Store macro in: This Workbook.
- Click OK.
- Apply filters to one or more columns using the Filter Drop-down menus.
How do I create a custom AutoFilter list? ›
To do this, select the Text Filter command from the table menu and choose one of its text filtering options. No matter which text filtering option you pick, Excel displays the Custom AutoFilter dialog box. This dialog box enables you to specify with great precision what records you want to appear on your filtered list.