VBA/Excel/Access/Word/Excel/AutoFilter

determine whether a ListObject object AutoFilter is turned on or off by testing its ShowAutoFilter property.

 
Sub showFilter()
     ActiveSheet.ListObjects("Table1").ShowAutoFilter = False
End Sub

filter a column to one of two customers, joined by the OR operator:

 
Sub SimpleOrFilter()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=4,Criteria1:="=A", Operator:=xlOr, Criteria2:="=B"
End Sub

Filtering Based on Color or Icon

 
Sub FilterByIcon()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6, _
        Criteria1:=ActiveWorkbook.IconSets(xl5ArrowsGray).Item(5),Operator:=xlFilterIcon
End Sub

filters to show records

 
Sub SimpleFilter()
    Worksheets("Sheet1").Select
    range("A1").autoFilter
    range("A1").autoFilter Field:=4, Criteria1:="=Agile Aquarium Inc."
End Sub

produces the top 10 revenue records:

 
Sub Top10Filter()
    " Top 12 Revenue Records
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6, Criteria1:="12",Operator:=xlTop10Items
End Sub

returns all customers that started with the letters A through E:

 
Sub SimpleAndFilter()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=4, _
        Criteria1:=">=A", _
        Operator:=xlAnd, Criteria2:="<=EZZ"
End Sub

Selecting a Dynamic Date Range Using AutoFilters

 
Sub DynamicAutoFilter()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=3,Criteria1:=xlFilterNextYear,Operator:=xlFilterDynamic
End Sub

Selecting Multiple Values from a Filter

 
Sub MultiSelectFilter()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=4, Criteria1:=Array("A", "C", "E","F", "H"),Operator:=xlFilterValues
End Sub

The AutoFilter object only exists when the AutoFilter feature is turned on. You can determine whether the Worksheet AutoFilter is active by using the value of the AutoFilterMode property

 
Sub filter()
     If ActiveSheet.AutoFilterMode Then
        MsgBox "Turned on"
     End If
End Sub

The fastest way to delete rows is provided by Excel"s AutoFilter feature:

 
     Sub DeleteRows3()
         Dim lLastRow As Long       "Last row
         Dim rng As range
         Dim rngDelete As range
         "Freeze screen
         Application.ScreenUpdating = False
         "Insert dummy row for dummy field name
         Rows(1).Insert
         "Insert dummy field name
         range("C1").value = "Temp"
         With ActiveSheet
             .UsedRange
             lLastRow = .cells.SpecialCells(xlCellTypeLastCell).row
             Set rng = range("C1", cells(lLastRow, "C"))
             rng.AutoFilter Field:=1, Criteria1:="Mangoes"
             Set rngDelete = rng.SpecialCells(xlCellTypeVisible)
             rng.AutoFilter
             rngDelete.EntireRow.delete
             .UsedRange
         End With
     End Sub

There is a Filters collection associated with the AutoFilter object that holds a Filter object for each field in the AutoFilter

 
Sub Main()
     With ActiveSheet.ListObjects(1)
         If .ShowAutoFilter Then
             With .AutoFilter.Filters(2)
                 If .On Then
                    MsgBox .Criteria1
                 End If
             End With
         End If
     End With
End Sub

To clear the filter from the customer colum

 
Sub SimpleFilter1()
    Worksheets("Sheet1").Select
    range("A1").autoFilter
    range("A1").autoFilter Field:=4
End Sub

To ensure that the worksheet AutoFilter is turned off

 
Sub fileterAuto()
     If ActiveSheet.AutoFilterMode Then
         ActiveSheet.autoFilter.range.autoFilter
     End If
End Sub

To find records that have a particular fill color, use an operator of xlFilterCellColor and specify a particular RGB value as the criteria.

 
Sub FilterByFillColor()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
End Sub

To find records that have a particular font color, use an operator of xlFilterFontColor and specify a particular RGB value as the criteria.

 
Sub FilterByFontColor()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6,Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
End Sub

To find records that have no conditional formatting icon, use an operator of xlFilterNoIcon and do not specify any criteria.

 
Sub FilterByIcon()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6,Criteria1:=ActiveWorkbook.IconSets(xl5ArrowsGray).Item(5),Operator:= xlFilterNoIcon
End Sub

To find records that have no fill color, use an operator of xlFilterNoFill and do not specify any criteria.

 
Sub FilterByFillColor()
    Worksheets("SalesReport").Select
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6, _
        Criteria1:=RGB(255, 0, 0), Operator:= xlFilterNoFill
End Sub

To switch off a range AutoFilter, you use the AutoFilter method of the Range object with no parameters:

 
Sub autoFilter()
     range("B3:D9").autoFilter
End Sub

turn on/off the AutoFilter drop-downs:

 
Sub TurnOnAutoFilter()
    " Turn on AutoFilters
    Worksheets("Sheet1").Select
    On Error Resume Next
    x = ActiveSheet.autoFilter.range.Areas.count
    If Err.Number > 0 Then
        ActiveSheet.range("A1").autoFilter
    End If
    On Error Resume Next
End Sub

turns off the drop-downs for Columns C, E, F, G, and H:

 
Sub AutoFilterCustom()
    range("A1").autoFilter Field:=3, VisibleDropDown:=False
    range("A1").autoFilter Field:=5, VisibleDropDown:=False
    range("A1").autoFilter Field:=6, VisibleDropDown:=False
    range("A1").autoFilter Field:=7, VisibleDropDown:=False
    range("A1").autoFilter Field:=8, VisibleDropDown:=False
End Sub

Use this code to turn off the AutoFilter drop-downs:

 
Sub TurnOffAutoFilter()
    Worksheets("Sheet1").Select
    On Error Resume Next
    x = ActiveSheet.autoFilter.range.Areas.count
    If Err.Number = 0 Then
        ActiveSheet.range("A1").autoFilter
    End If
    On Error Resume Next
End Sub