Filtering Tricks‎ > ‎

Auto-Autofilter

Sample file for this exercise:   Auto-AutofilterMacro.xls


PROBLEM:
"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."

SPECIFICATIONS:
  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

CODE

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
    Else
        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("AA:AA").ClearContents
Columns(AFCol).AutoFilter
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!



Comments