Filtering Tricks‎ > ‎


Sample file for this exercise:   Auto-AutofilterMacro.xls

"I have a list of stores in one table. I have Problem tickets with associated store numbers in another. I want to be able to press a button to Autofilter by store number and have it cycle through the list i.e.
"So I would push the button and it would filter all problems at store 1. Bush the button again and it would be store 2. Push again and store 3. The list to scroll through is Store List."

  1. Button-based macro to activate Autofilter
  2. Immediately display all rows based on first value in key column
  3. Each button-press autofilters for next value


Option Explicit
Public CurrCrit As String
Const AFCol As Long = 2    'Autoflter column: 1=column A, 2=column B, etc...

Sub AutofilterRotate()
'Jerry Beaucaire   5/3/2010
'Filter by column AFCol, each button click shows another filter result
Dim rFind As Long

Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

    If CurrCrit = "" Then
        CurrCrit = Cells(2, AFCol).Value
        Columns(AFCol).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Range("AA1"), Unique:=True
        rFind = Columns("AA:AA").Find(CurrCrit, After:=[AA1], _
            LookIn:=xlValues, LookAt:=xlWhole).Row
        CurrCrit = Range("AA" & rFind + 1)
        If CurrCrit = "" Then CurrCrit = Range("AA2").Value
    End If
Columns(AFCol).AutoFilter Field:=1, Criteria1:=CurrCrit
Application.ScreenUpdating = False
End Sub

Sub ShowAll()
    ActiveSheet.AutoFilterMode = False
End Sub

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!