VBA/Excel/Access/Word/Excel/AutoFilter

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

   <source lang="vb">

Sub showFilter()

    ActiveSheet.ListObjects("Table1").ShowAutoFilter = False

End Sub

</source>
   
  

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

   <source lang="vb">

Sub SimpleOrFilter()

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

End Sub

</source>
   
  

Filtering Based on Color or Icon

   <source lang="vb">

Sub FilterByIcon()

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

End Sub

</source>
   
  

filters to show records

   <source lang="vb">

Sub SimpleFilter()

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

End Sub

</source>
   
  

produces the top 10 revenue records:

   <source lang="vb">

Sub Top10Filter()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub SimpleAndFilter()

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

End Sub

</source>
   
  

Selecting a Dynamic Date Range Using AutoFilters

   <source lang="vb">

Sub DynamicAutoFilter()

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

End Sub

</source>
   
  

Selecting Multiple Values from a Filter

   <source lang="vb">

Sub MultiSelectFilter()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub filter()

    If ActiveSheet.AutoFilterMode Then
       MsgBox "Turned on"
    End If

End Sub

</source>
   
  

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

   <source lang="vb">

    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
</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

To clear the filter from the customer colum

   <source lang="vb">

Sub SimpleFilter1()

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

End Sub

</source>
   
  

To ensure that the worksheet AutoFilter is turned off

   <source lang="vb">

Sub fileterAuto()

    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.autoFilter.range.autoFilter
    End If

End Sub

</source>
   
  

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

   <source lang="vb">

Sub FilterByFillColor()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub FilterByFontColor()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub FilterByIcon()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub FilterByFillColor()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub autoFilter()

    range("B3:D9").autoFilter

End Sub

</source>
   
  

turn on/off the AutoFilter drop-downs:

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>