VBA Add Filter to table in Excel

Excel, Programming, Table / , , , , , , , , , , , ,
VBA Add Filter to table

VBA Add Filter to table in Excel. 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, and icons etc. Let us see how to apply filter in the following tutorial. We have explained with multiple examples with screenshot output.

Table of Contents:

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.

VBA Add Filter to Table

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.

BA Apply Filter on single column to table

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.
VBA Apply Filter on multiple columns to table

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel VBA Tables and ListObjects

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment