AutoFilter Object

Worksheets (Worksheet) AutoFilter Filters (Filter)

Represents autofiltering for the specified worksheet.

Using the AutoFilter Object

Use the AutoFilter property to return the AutoFilter object. Use the Filters method to return a collection of individual column filters. Use the Range method 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.

  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.

  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)
        Else
            w.Range(currentFiltRange).AutoFilter field:=col, _
                Criteria1:=filterArray(col, 1)
        End If
    End If
Next
End Sub