This Excel tutorial explains how to use Excel VBA to add AutoFilter, cancel AutoFilter, add sorting in Worksheet AutoFilter.
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 Table 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.
For Table AutoFilter, click here.
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.
Similar to worksheet AutoFilter, you can apply on any cell of header, apply on selected header, or apply on full header with data range.
For example, 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
Instead of selecting specific Range, you can simply apply to all used Range
ActiveSheet.UsedRange.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.
Similar to adding AutoFilter, you can cancel on a header Range, on selected header, or apply on full header with data range.
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.
expression .AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
| 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.
|
||||||||||||||||||||||||||||||||||||
| 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
To apply multiple sorting, for example, to sort Column A and then sort Column B,
ActiveSheet.AutoFilter.Sort.SortFields.Clear ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range ("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
ActiveSheet.AutoFilter.Sort.SortFields.Clear ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range ("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
ActiveSheet.AutoFilter.Sort.Apply
Note that in Excel Worksheet, the equivalent action would be sorting Column B and then sorting Column A.
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