Here is a simple way of using multiple criteria in the Excel AutoFilter option
Sub AutoFilter_WithMultiple_Criteria()
Dim oWS As Worksheet
On Error GoTo Err_Filter
oWS = ActiveSheet
oWS.UsedRange.AutoFilter(Field:=2, Criteria1:="Apple", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange")
Finally:
If Not oWS Is Nothing Then oWS = Nothing
Err_Filter:
If Err <> 0 Then
MsgBox(Err.Description)
Err.Clear()
GoTo Finally
End If
End Sub
See also:
Create AutoFilter with Multiple Criteria using Excel VBA
AutoFilter using Excel VBA
Check for existence of Filter using Excel VBA
Excel Filter Show All using VBA
Retrieve / Get First Row of Excel AutoFilter using VBA
Posts
That's only two criteria, not multiple
ReplyDeleteWhat you mean multiple?
ReplyDeletethis can be already seen recording macro. :(
i keep getting -
ReplyDeletecompile error syntax error ?
oWS.UsedRange.AutoFilter(Field:=1, Criteria1:="1", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange")
Which version of Excel are you using? Can you please elaborate on the error it throws if possible?
ReplyDeleteHi can i get VB script for below example,it should select Active=testing into one another excel sheet,Active=integration into another excel sheet and remaining all ="active" except testing & Designing into another excel sheet
ReplyDelete1 shankar Active Testing
2 Venkat Terminated Intrgration
3 shilpa Active Designing
4 Mohan Active Testing
5 bindhu Active Designing
6 Sunny Terminated testing
I am using similar coding but I have 1 filter on column 1 and need to apply 3 "or" filters to column 2. It can cope with the initial filter on column 1 and 2 "or" filters on column 2 but when I add a 3rd filter to column 2 it gives me the application or object defined error.
ReplyDeleteAutoFilter(Field:=1, Criteria1:="1", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange",Operator:=XlAutoFilterOperator.xlOr,Criteria3:="Peach" )
If I comment out the 3rd operator and criteria, it works fine. Is there a limit to the number of criteria you can filter? All of the examples I have found online only have 2 criteria
Excel autofilter function only allows 1 or 2 criteria. If you want more, you have to create a custom function to duplicate what autofilter does (compares criteria and then hides rows that don't match)
ReplyDeleteYou can use an array as criteria1 with xlFilterValues as the operator.
ReplyDeleterngData.AutoFilter Field:=1, Criteria1:=array("1","2","3"), Operator:=xlFilterValues
Swaroop : Try this syntax
ReplyDeleteActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:="Apple", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange"
rngData.AutoFilter Field:=1, Criteria1:=array("1","2","3"), Operator:=xlFilterValues
ReplyDeletethis is the best solution
thnks
rj
Except it doesn't work. It returns the last value from array in filtered data - "3".
ReplyDeleteCorrect code, without syntax errors (at least with Excel 2k13). IMO xlFilterValues + Criteria1 Array is the best option
ReplyDeleteNice blog. Keep going, I use to get here almost everytime I wonder how to do on VBA...
Sub AutoFilter_WithMultiple_Criteria()
Dim oWS As Excel.Worksheet
Dim intField As Integer
On Error GoTo Err_Filter
Set oWS = ActiveSheet
'!!!!!
intField = 2
'!!!!!
oWS.UsedRange.AutoFilter Field:=intField, Criteria1:=Array("Apple", "Orange", "Grape"), _
Operator:=XlAutoFilterOperator.xlFilterValues '.xlAnd '.xlOr
Finally:
If Not oWS Is Nothing Then _
Set oWS = Nothing
On Error GoTo 0
Err_Filter:
If Err <> 0 Then
MsgBox (Err.Description)
Err.Clear '()
GoTo Finally
End If
End Sub
Still does not work keeps on returning the last value of the array example: always and only "Grape"
DeleteI'm having the same problem
Delete