VBA Add Filter to table in Excel
VBA Add Filter to table. It filters a list by using AutoFilter method in Excel VBA. We can apply or add filter to table on single column or columns. We can filter different types of data like date, numbers, string, boolean, colors, icons etc. Let us see how to apply filter in the following tutorial. We have explained with multiple examples with output.
Syntax to Add or Apply VBA Filter to Table
Here is the Syntax to apply or add filter to table in Excel.
expression.AutoFilter([Field], [Criteria1], [Operator As XLAutoFilterOperator = xlAnd],[Criteria2], [VisibleDropDown], [SubField])
It returns a variant data type.
where
expression is a range.
All the below fields are optional parameters and consists of variant data type except operator. It consists of the XlAutoFilterOperator data type.
Field: It represents the field offset integer value. The leftmost field starts with one(1).
Criteria1: It represents the first criteria.
Operator: It represents the type of filter.
Criteria2: It represents the second criteria.
VisibleDropDown: Default value is True. It represents the autofilter drop down arrow for the filtered field to show. If it is False, hide the autofilter drop down arrow for the filtered field.
SubField: It represents the field to apply criteria.
Apply Filter to Table in Excel VBA
Here is an example macro code to apply filter to table using VBA in Excel. The following procedure just adds filter to table.
'VBA apply Filter to table Sub VBAF1_Apply_Filter_to_table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Add filter to table loTable.Range.AutoFilter End Sub
Note: If filter is already applied to table, then the above procedure removes filter from table.
Output: Here is the output screenshot of above example macro code.
Apply Filter on Single Column in Excel VBA
Let us see the example vba procedure to apply filter on single column to table in Excel. In this procedure ‘MyTable’ represents the table name. You can change table name as per your requirement. And ‘Table’ represents the Excel sheet name. You can change this one as well.
'Apply Filter to table on single column in Excel VBA Sub VBAF1_Add_Filter_to_table_Single_Column() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Add filter to table loTable.Range.AutoFilter Field:=2, Criteria1:=26 End Sub
Output: Here is the output screenshot of above example macro code.
VBA Apply Filter on Multiple Columns
Here is another example vba procedure to apply filter on multiple columns to table in Excel. As mentioned above ‘MyTable’ represents the table name. You can change table name as per your requirement. And ‘Table’ represents the Excel sheet name. You can change this one as well.
'VBA apply Filter to table on multiple columns Sub VBAF1_Apply_Filter_to_table_Multiple_Columns() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Add filter to table loTable.Range.AutoFilter Field:=2, Criteria1:=26 '1st Criteria loTable.Range.AutoFilter Field:=3, Criteria1:=40 '2nd Criteria End Sub
Output: Here is the output screenshot of above example macro code.
Instructions to use example macro
You can follow step by step instructions to run above macro example VBA code.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- Now you can see the example output screenshot after VBA macro code.
Other Related References
Click on the following links for related reference articles. It helps for your reference.