Skip to main content

Range.AutoFilter method

Filters a list by using the AutoFilter.

Syntax

expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)

expression An expression that returns a Range object.

Parameters

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, "<>" to find non-blank fields, and "><" to select (No Data) fields in data types.

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 An XlAutoFilterOperator constant specifying the type of filter.
Criteria2 Optional Variant The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria. Also used as single criteria on date fields filtering by date, month or year. Followed by an Array detailing the filtering Array(Level, Date). Where Level is 0-2 (year,month,date) and Date is one valid Date inside the filtering period.
SubField Optional Variant The field from a data type on which to apply the criteria (for example, the "Population" field from Geography or "Volume" field from Stocks). Omitting this value targets the "(Display Value)".
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.

Return value

Variant

Remarks

If you omit all the arguments, this method simply toggles the display of the AutoFilter drop-down arrows in the specified range.

Excel for Mac does not support this method. Similar methods on Selection and ListObject are supported.

Unlike in formulas, subfields don't require brackets to include spaces.

Example

This example filters a list starting in cell A1 on Sheet1 to display only the entries in which field one is equal to the string Otis. The drop-down arrow for field one will be hidden.

Worksheets("Sheet1").Range("A1").AutoFilter _
    Field:=1, _
    Criteria1:="Otis", _
    VisibleDropDown:=False

This example filters a list starting in cell A1 on Sheet1 to display only the entries in which the values of field one contain a SubField, Admin Division 1 (State/province/other), where the value is Washington.

Worksheets("Sheet1").Range("A1").AutoFilter _
    Field:=1, _
    Criteria1:="Washington", _
    SubField:="Admin Division 1 (State/province/other)"

This example filters a table, Table1, on Sheet1 to display only the entries in which the values of field one have a "(Display Value)" that is either 1, 3, Seattle, or Redmond.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, _
    Criteria1:=Array("1", "3", "Seattle", "Redmond"), _
    Operator:=xlFilterValues

Data types can apply multiple SubField filters. This example filters a table, Table1, on Sheet1 to display only the entries in which the values of field one contain a SubField, Time Zone(s), where the value is Pacific Time Zone, and where the SubField named Date Founded is either 1851 or there is "(No Data)".

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, _
    Criteria1:="Pacific Time Zone", _
    SubField:="Time Zone(s)"
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, _
    Criteria1:=Array("1851", "><"), _
    Operator:=xlFilterValues, _
    SubField:="Date founded"

This example filters a table, Table1, on Sheet1 to display the Top 10 entries for field one based off the Population SubField.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, _
    Criteria1:="10", _
    Operator:=xlTop10Items, _
    SubField:="Population"

This example filters a table, Table1, on Sheet1 to display the all entries for January 2019 and February 2019 for field one. There does not have to be a row containing January the 31.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, _
    Criteria2:=Array(1, "1/31/2019", 1, "2/28/2019")

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>