Excel VBA Add Table AutoFilter cancel AutoFilter sorting

This Excel tutorial explains how to use Excel VBA to add Table AutoFilter, cancel Table AutoFilter, sorting Table.

You may also want to read:

Sort data using custom Function by Bubble Sort

Sort data using custom Function by assigning number to Text

Sort data using Range Sort Method

Sort data using Worksheet AutoFilter

Excel VBA Add Table AutoFilter or Cancel AutoFilter

Using VBA to add AutoFilter or cancel Auto Filter is very simple if you know the code, but you should note that Excel does not treat Worksheet AutoFilter the same way as Table AutoFilter.

This article only talks about Table AutoFilter.

For Worksheet AutoFilter, click here.

Excel VBA Add Table AutoFilter

You can only have one Auto Filter in one Worksheet. However if you have Table, you can have one Worksheet Auto Filter, plus one Filter for each Table. AutoFilter in Table is an Object for each Table.

Table is a ListObjects, you can access the AutoFilter of Table by specifying Table name¬†ListObjects(“Table_name”) or through ListObjects Array. If you have two Table in the worksheet, the first added Table is ListObjects(1) and the other is ListObjects(2).

Add Table AutoFilter

The below code add AutoFilter for ListObjects(1). If AutoFilter is already present, nothing will happen.

Dim LstObj As ListObject
Set LstObj = ActiveSheet.ListObjects(1)
LstObj.ShowAutoFilter = True

Cancel Table AutoFilter

The below code cancel AutoFilter for ListObjects(1). If AutoFilter is not present, nothing will happen.

Dim LstObj As ListObject
Set LstObj = ActiveSheet.ListObjects(1)
LstObj.ShowAutoFilter = False

Unhide filtered data

The below code unhide AutoFilter for ListObjects(1).

Dim LstObj As ListObject
Set LstObj = ActiveSheet.ListObjects(1) 
If LstObj.AutoFilter.FilterMode Then 
   LstObj.AutoFilter.ShowAllData 
End If

Apply criteria to Table AutoFilter

The syntax is exactly the same as Worksheet AutoFilter because both of them use AutoFilter Method of Range. the only difference is that you have to assess the Range Object of ListObject first.

The below example shows how to assess Range of LstObj in order to use AutoFilter Method.

LstObj.Range.AutoFilter Field:=1, Criteria1:=”A”, Operator:=xlOr, Criteria2:=”D”

Read the syntax of AutoFilter Method before you read on.

expression .AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
Name Requiredl Data Type Description
Field Optional Variant The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).
Criteria1 Optional Variant The criteria (a string; for example, “101”). Use “=” to find blank fields, or use “<>” to find nonblank fields. If this argument is omitted, the criteria is All. If Operator is xlTop10Items, Criteria1 specifies the number of items (for example, “10”).
Operator Optional XlAutoFilterOperator One of the constants of XlAutoFilterOperator specifying the type of filter.

Name Value Description
xlAnd 1 Logical AND of Criteria1 and Criteria2.
xlBottom10Items 4 Lowest-valued items displayed (number of items specified in Criteria1).
xlBottom10Percent 6 Lowest-valued items displayed (percentage specified in Criteria1).
xlFilterCellColor 8 Color of the cell
xlFilterDynamic 11 Dynamic filter
xlFilterFontColor 9 Color of the font
xlFilterIcon 10 Filter icon
xlFilterValues 7 Filter values
xlOr 2 Logical OR of Criteria1 or Criteria2.
xlTop10Items 3 Highest-valued items displayed (number of items specified in Criteria1).
xlTop10Percent 5 Highest-valued items displayed (percentage specified in Criteria1).
Criteria2 Optional Variant The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria.
VisibleDropDown Optional Variant True to display the AutoFilter drop-down arrow for the filtered field. False to hide the AutoFilter drop-down arrow for the filtered field. True by default.

The below examples summarize all the basics you need to know about AutoFilter Criteria.

Example Explanation
LstObj.Range.AutoFilter Field:=1, Criteria1:=”b” Filter text criteria
LstObj.Range.AutoFilter Field:=1, Criteria1:=”b*” Filter text criteria with Wildcard
LstObj.Range.AutoFilter Field:=2, Criteria1:=”>10″ Filter number criteria
LstObj.Range.AutoFilter Field:=1, Criteria1:=”b”, _
Operator:=xlOr, Criteria2:=”c”
Filter two text criteria using OR, which means the result shows b and c. Note that you can only have 2 criteria at most
LstObj.Range.AutoFilter Field:=1, Criteria1:= Array(“a”, “b”, “c”), Operator:=xlFilterValues Filter two text criteria using Array
LstObj.Range.AutoFilter Field:=1, Criteria1:=”b”
LstObj.Range.AutoFilter Field:=2, Criteria1:=”>10″
Filter two different Fields

Apply Table AutoFilter sorting

Before applying sorting, you should clear previous sorting first. To clear sorting in AutoFilter, use Sort.SortFields.Clear Method. Note that clear sorting does not reverse the data to the original order.

To tell AutoFilter how you want to sort the data, use Sort.SortFields.Add Method. (no data is sorted at this point)

Name Required/Optional Data Type Description
Key Required Range Specifies a key value for the sort.
SortOn Optional Variant The field to sort on.
Order Optional Variant Specifies the sort order.
CustomOrder Optional Variant Specifies if a custom sort order should be used.
DataOption Optional Variant Specifies the data option.

Finally, use Sort.Apply Method  to apply on the sort states.

The below example sorts data in Ascending Order in column A.

Dim LstObj As ListObject
Set LstObj = ActiveSheet.ListObjects(1)
LstObj.Sort.SortFields.Clear
LstObj.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
LstObj.Sort.Apply

Reset Table Filter

The below code unhide all hidden data and cancel sorting.

Dim LstObj As ListObject
Set LstObj = ActiveSheet.ListObjects(1)
LstObj.ShowAutoFilter = False
LstObj.ShowAutoFilter = True
LstObj.Sort.SortFields.Clear

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *