Edit

Share via

AutoFilter object (Excel)

  • Article

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.

Example

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

Methods

Properties

See also

Support and feedback

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.