The AutoFilter method of the Range object allows you to programmatically apply AutoFilter to a list according to criteria specified in its parameters:
expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
- expression — a reference to a cell in the range or to the range itself that will be filtered.
- Field — optional parameter specifying the number of the field (column) in which data will be filtered. Numbering starts from the leftmost field, where the first field has the number 1.
- Criteria1 and Criteria2 — optional parameters specifying up to two criteria for filtering the field. You can use a string constant, for example « 101 », and relational operators >, <, >=, <=, =, <>. Use = to filter empty fields and <> to filter non-empty fields. If the parameter is omitted, the criterion is All. If the parameter Operator equals xlTop10Items, then Criteria1 defines the number of displayed items (for example, « 30 »).
- Operator — optional parameter. Valid values are the following XlAutoFilterOperator constants:
- xlAnd (logical AND of the first and second filter criteria),
- xlOr (logical OR of the first and second criteria),
- xlTop10Items (displays the top 10 items of the field),
- xlBottom10Items (displays the bottom 10 items of the field),
- xlTop10Percent (displays the top 10% of items),
- xlBottom10Percent (displays the bottom 10% of items).
- VisibleDropDown — optional parameter (Boolean). Determines whether drop-down arrows are shown. Default is True.
When writing VBA programs that use AutoFilter, the following methods and properties are also helpful:
- The AutoFilter method of the Range object, used without parameters, displays or hides the AutoFilter arrows.
- The ShowAllData method of the Worksheet object displays all data in the list—both filtered and unfiltered. Example:
- Worksheets(« Orders »).ShowAllData
- The AutoFilterMode (read-only) property of the Worksheet object returns True if AutoFilter arrows are present on the worksheet.
- The FilterMode (read-only) property of the Worksheet object returns True if filtered data with hidden rows exists on the worksheet.
The AutoFilter object encapsulates the data about the AutoFilter used on a worksheet. This object is returned by the AutoFilter property of the Worksheet object.
Table. Main properties of the AutoFilter object
| Property | Description |
| Filters | Returns a collection of Filter objects, i.e., all filters that form the given AutoFilter. The properties of the Filter object are given in Table 7.2. For the Filters collection, the main properties are Count and Item, which return the number of items and a specific item in the collection. |
| Range | Returns the range to which the filter is applied. |
Table. Properties of the Filter object
| Property | Description |
| On | Returns True if the filter is applied. |
| Criteria1 | Returns the first filter criterion. |
| Criteria2 | Returns the second filter criterion. |
| Operator | Returns the filter operator. Valid values are the XlAutoFilterOperator constants. |
Note also that the EnableAutoFilter property of the worksheet allows you to control access to AutoFilter drop-down lists on a protected sheet. If its value is True, the AutoFilter drop-down lists are available to the user even when the worksheet is protected. In this case, protection must be enabled for the user interface only:
ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
As an example, consider a procedure using the AutoFilter object. In the standard module, place the procedure code that first determines whether an AutoFilter exists on the worksheet, and if so, retrieves the total number of filters and the filter criteria for the applied custom AutoFilters. In the Sheet1 module, place the code that handles the button click.