Autofilter multiple criteria in 2003 - more than 2

[UHsoccer]

Using Excel 2003.
Is there a limit on the number of criteria that can be used in a VB macro for Autofilter.

I tried to create them in a "for loop" as follows

Sub Define_Autofilter_Test()
 
    '  assumes that data to be filtered is in column A
    '  test content could be : Prod1, prod2, prod3, prod4 entries
 
    Dim CritArr(10) As String
    numCrit = 3
 
    CritArr(1) = "Prod1"   '
    CritArr(2) = "Prod2"
    CritArr(3) = "Prod3"
 
    crit1 = CritArr(1)
    crit2 = CritArr(2)
 
    For num = 3 To numCrit
        addedCrit = addedCrit & ", Operator:=xlOr, Criteria" & num & ":=" & CritArr(num)
    Next num
 
    Columns("A:A").Select  '  what column to filter
    Selection.AutoFilter Field:=1, Criteria1:=crit1, Operator:=xlOr, Criteria2:=crit2 & addedCrit  ' filter displayed
 
' for msgbox  display   
    OrigCrit = "Field:=1, Criteria1:=" & crit1 & ", Operator:=xlOr, Criteria2:=" & crit2
    NetCrit = OrigCrit & addedCrit
 
'   result is
'   Selection.AutoFilter Field:=1, Criteria1:=Prod1, Operator:=xlOr, Criteria2:=Prod2, Operator:=xlOr, Criteria3:=Prod3
 
    temp = MsgBox("Filter needed by : " & crit1 & " and " & crit2 & " and " & CritArr(3) & vbCr & vbCr & _
                    "Original Criteria : " & OrigCrit & vbCr & vbCr & _
                    "Added   Criteria : " & addedCrit & vbCr & vbCr & _
                    "Final     Criteria : " & NetCrit, vbInformation + vbOKOnly, "Filter results")
End Sub

It only uses filter 1 (Prod1).
It seems to have a problem when there are more than TWO (2) criteris defined

Please comment

 

[Jonmo1]

Yes, you can only filter on 2 criteria.

You'll need an alternate method.

I'd suggest a helper column.
Put a formula like =AND(Criteria1,criteria2,criteria3)

Then filter on that column for TRUE.

 

[UHsoccer]

Is that also a restriction in 2007?

 

[Jonmo1]

No. One of many improvements in 2007.

 

[UHsoccer]

jonmo1,

I implemented the "helper" column method that you suggested.

Nicely solved for both 2003 and 2007

Thanks