How to make a macro auto-filter multiple criteria?

Jimi Sherman
Jimi Sherman used Ask the Experts™
on
Hi, I need to make a macro with an auto-filter using multiple criteria in column A.
The multiple criteria are "S*" and "P*".
Thanks,
Jimi
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this macro:
Sub Filter()

    ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:="=P", _
        Operator:=xlOr, Criteria2:="=S"
        
End Sub

Open in new window

Flyster
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Although you can use the Or operator to filter two items, that approach breaks when you want a third. The Array method in the macro below allows you to add as many items as you like to the filter.
Sub AutofilterGenie()
Dim rg As Range
Set rg = Range("A1")            'Header label for column A
rg.AutoFilter                   'Remove any existing AutoFilter
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))  'All the data in column A
rg.AutoFilter Field:=1, Criteria1:=Array("S*", "P*"), Operator:=xlFilterValues
End Sub

Open in new window

Author

Commented:
Thanks
Jimi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial