AutoFilter object (Excel)
Represents autofiltering for the specified worksheet.
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.
Note
Working with objects (for example, the Interior object) requires adding a reference to an object. You'll find more information about assigning an object reference to a variable or property in the Set statement.
Use the AutoFilter property of the Worksheet object 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.
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 by 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
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.