Excel VBA Add Autofilter Cancel AutoFilter sorting in worksheet

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

Excel VBA Add 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 Worksheet AutoFilter.

Excel VBA AutoFilter in Worksheet

Add AutoFilter

AutoFilter is an Range Method. To add AutoFilter, you must specify the Cell which you want to add filter on. If you apply on A1, the Cells adjacent to A1 will have AutoFilter applied.

Range("A1").AutoFilter

You can specify which fields you need to apply AutoFilter to avoid adding AutoFilter applying on unwanted adjacent Cells.

Range("A1:A3").AutoFilter

Note that AutoFilter Method can also be used to cancel AutoFilter, if AutoFilter already exists.

Cancel AutoFilter

Because you can only have one AutoFilter in one Worksheet, you can check whether a Worksheet has AutoFilter using AutoFilterMode Property.

The below code cancel AutoFilter if found any.

If ActiveSheet.AutoFilterMode = True Then
   Range("A1").AutoFilter
End If

Unhide filtered data

You can use Worksheet Property FilterMode to check if there are filtered data in the worksheet. Use ShowAllData Method to unhide data.

If ActiveSheet.FilterMode = True Then
     ActiveSheet.ShowAllData
End If

Apply criteria to AutoFilter

Read the syntax of AutoFilter Method before you read on.

Name	Required/Optional	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
Range(“A1″).AutoFilter Field:=1, Criteria1:=”b”	Filter text criteria
Range(“A1″).AutoFilter Field:=1, Criteria1:=”b*”	Filter text criteria with Wildcard
Range(“A1″).AutoFilter Field:=2, Criteria1:=”>10″	Filter number criteria
Range(“A1″).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
Range(“A1″).AutoFilter Field:=1, Criteria1:= Array(“a”, “b”, “c”), Operator:=xlFilterValues	Filter two text criteria using Array
Range(“A1″).AutoFilter Field:=1, Criteria1:=”b” Range(“A1″).AutoFilter Field:=2, Criteria1:=”>10″	Filter two different Fields

Apply 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.

ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet..AutoFilter.Sort.SortFields.Add Key:=Range ("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
ActiveSheet..AutoFilter.Sort.Apply

Reset Filter

This is a summary of all the above tutorials. Unhide all hidden data and cancel sorting.

If ActiveSheet.AutoFilterMode = False Then
   Range("A1").AutoFilter
Else 
 ActiveSheet.AutoFilter.Sort.SortFields.Clear
 If ActiveSheet.FilterMode = True Then
     ActiveSheet.ShowAllData
 End If
End If

Excel VBA Add Autofilter Cancel AutoFilter sorting in worksheet