AutoFilter Object

Excel Developer Reference

Represents autofiltering for the specified worksheet.


Use the AutoFilter property to return the AutoFilter object. Use the Filters property to return a collection of individual column filters. Use the Range property to return the Range object that represents the entire filtered range. The following example stores the address and filtering criteria for the current filtering and then applies new filters.

Visual Basic for Applications
  Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String

Sub ChangeFilters()

Set w = Worksheets("Crew") With w.AutoFilter currentFiltRange = .Range.Address With .Filters ReDim filterArray(1 To .Count, 1 To 3) For f = 1 To .Count With .Item(f) If .On Then filterArray(f, 1) = .Criteria1 If .Operator Then filterArray(f, 2) = .Operator filterArray(f, 3) = .Criteria2 End If End If End With Next End With End With

w.AutoFilterMode = False w.Range("A1").AutoFilter field:=1, Criteria1:="S"

End Sub

To create an AutoFilter object for a worksheet, you must turn autofiltering on for a range on the worksheet either manually or using the AutoFilter method of the Range object. The following example uses the values stored in module-level variables in the previous example to restore the original autofiltering to the Crew worksheet.

Visual Basic for Applications
  Sub RestoreFilters()
Set w = Worksheets("Crew")
w.AutoFilterMode = False
For col = 1 To UBound(filterArray(), 1)
    If Not IsEmpty(filterArray(col, 1)) Then
        If filterArray(col, 2) Then
            w.Range(currentFiltRange).AutoFilter field:=col, _
                Criteria1:=filterArray(col, 1), _
                    Operator:=filterArray(col, 2), _
                Criteria2:=filterArray(col, 3)
            w.Range(currentFiltRange).AutoFilter field:=col, _
                Criteria1:=filterArray(col, 1)
        End If
    End If
End Sub

Bb210085.vs_note(en-us,office.12).gif  Note
When using AutoFilter with dates, the format should be consistent with English date separators ("/") instead of local settings ("."). A valid date would be "2/2/2007", whereas "2.2.2007" is invalid.

See Also