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.

VBA Tables Tables Examples