Tuesday, August 12, 2008

Create AutoFilter with Multiple Criteria using Excel VBA

Excel VBA Applying Multiple Criteria in AutoFilter


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

13 comments:

  1. That's only two criteria, not multiple

    ReplyDelete
  2. What you mean multiple?
    this can be already seen recording macro. :(

    ReplyDelete
  3. Anonymous6:43 PM

    i keep getting -
    compile error syntax error ?

    oWS.UsedRange.AutoFilter(Field:=1, Criteria1:="1", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange")

    ReplyDelete
  4. Which version of Excel are you using? Can you please elaborate on the error it throws if possible?

    ReplyDelete
  5. Anonymous5:56 PM

    Hi 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

    1 shankar Active Testing
    2 Venkat Terminated Intrgration
    3 shilpa Active Designing
    4 Mohan Active Testing
    5 bindhu Active Designing
    6 Sunny Terminated testing

    ReplyDelete
  6. Anonymous3:56 AM

    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.

    AutoFilter(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

    ReplyDelete
  7. Anonymous5:42 PM

    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)

    ReplyDelete
  8. Anonymous10:16 AM

    You can use an array as criteria1 with xlFilterValues as the operator.


    rngData.AutoFilter Field:=1, Criteria1:=array("1","2","3"), Operator:=xlFilterValues

    ReplyDelete
  9. Swaroop : Try this syntax

    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:="Apple", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange"

    ReplyDelete
  10. Anonymous9:25 AM

    rngData.AutoFilter Field:=1, Criteria1:=array("1","2","3"), Operator:=xlFilterValues

    this is the best solution

    thnks
    rj

    ReplyDelete
  11. Except it doesn't work. It returns the last value from array in filtered data - "3".

    ReplyDelete
  12. Correct code, without syntax errors (at least with Excel 2k13). IMO xlFilterValues + Criteria1 Array is the best option

    Nice 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

    ReplyDelete
    Replies
    1. Still does not work keeps on returning the last value of the array example: always and only "Grape"

      Delete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group