Example

Use the property to return the AutoFilter object. Use the property to return a collection of individual column filters. Use the 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 method of the 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) Else w.Range(currentFiltRange).AutoFilter field:=col, _ Criteria1:=filterArray(col, 1) End If End If Next End Sub
Auto Filter Object autofilter autofilter object

See also: