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
It only uses filter 1 (Prod1).
It seems to have a problem when there are more than TWO (2) criteris defined
Please comment
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
Code:
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