none
MULTI-FILTER RRS feed

  • Question

  • Good morning all.

    I'm interested in creating a custom filter tool that will allow me to select several elements, under a single column. 

    Eg., 

    ActiveSheet.Range("$A$1:$N$6438").AutoFilter Field:=4, Criteria1:=">=3514" _
            , Operator:=xlAnd, Criteria2:="<=3517"

    This one I'd recorded, and it only allows 2 filtered elements. I'm in need of 4-7 filtered items.

    How would I accomplish that?

    ActiveSheet.Range("$A$1:$N$6438").AutoFilter Field:=4, Criteria1:="=*3514*" _
            , Operator:=xlAnd, Criteria2:="=*3517*"  , Operator:=xlAnd, Criteria3:="=*3416*" _

     , Operator:=xlAnd, Criteria4:="=*3612*" ........ etc?

    Then, I'm thinking that to do this, I want to have input boxes, or, just a user form (several input fields) that allows me to input my desired items.  

    having tinkered with my idea, I did the following:

    I've created a user form, using 7 RefEdit (RefEdit1 through RefEdit7) boxes, and a command button. I obtain a 1004 error, aka, an application-defined, or an object-defined error, so it appears here is where I need the help.

    my code is--

    Private Sub CommandButton1_Click()

    ActiveSheet.Range("$A$1:$N$6438").AutoFilter Field:=4, Criteria1:="=" & RefEdit1.Value _
            , Operator:=xlAnd, Criteria2:="=" & RefEdit2.Value, Operator:=xlAnd, Criteria2:="=" & RefEdit2.Value _
            , Operator:=xlAnd, Criteria3:="=" & RefEdit3.Value, Operator:=xlAnd, Criteria4:="=" & RefEdit4.Value _
            , Operator:=xlAnd, Criteria5:="=" & RefEdit5.Value, Operator:=xlAnd, Criteria6:="=" & RefEdit6.Value _
            , Operator:=xlAnd, Criteria7:="=" & RefEdit7.Value

    End Sub

    TYIA.

    Best.

    Wednesday, September 2, 2015 5:45 PM

Answers

  • This syntax only allows for Criteria1 and Criteria2. There is no support for Criteria3 etc.

    Instead, set Operator to xlFilterValues, and set Criteria1 to an array that specifies the values. You have to specify a series of fixed values though, this method doesn't support wildcards. For example:

        Dim arr(1 To 4) As String
        arr(1) = "1234"
        arr(2) = "2345"
        arr(3) = "3456"
        arr(4) = "4567"
        Range("A1:N6438").AutoFilter Field:=4, Criteria1:=arr, Operator:=xlFilterValues

    If you want multiple criteria with wildcards, you have to use Advanced Filter instead of AutoFilter.

    Set up a criteria range, e.g. P1:P5.

    In P1, enter the field name (column header) of the field you want to filter on.

    In P2 to P5, enter the criteria. For example:

    ID
    *3512*
    *3514*
    *3516*
    *3518*

    Now you can use code like this:

        Range("A1:N6438").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("P1:P5")

    You can fill the criteria range programmatically of course.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SteveDB1 Wednesday, September 2, 2015 7:55 PM
    Wednesday, September 2, 2015 7:18 PM
  • Hi Hans.

    Thank you for your response.

    Ok..... I've tinkered with your code a bit, and found what I wanted. 

    Here's my modifications for a multi-filter, with 7 criteria. Apparently, turning it to an array is the trick (Thank you-- BIG TIME :-D )

        

    Public Sub CommandButton1_Click()

    Dim arr(1 To 7) As String
    Dim Col As Variant
    'Because Excel is limited to 2 criteria for filtering
    ' this code is set up to filter 7 criteria.
    ' The array elements which have no value assigned to them
    ' will show a blank cell. So, keep that in mind
    ' when seeing results.
     Col = RefEdit8.Value

        arr(1) = RefEdit1.Value
        arr(2) = RefEdit2.Value
        arr(3) = RefEdit3.Value
        arr(4) = RefEdit4.Value
        arr(5) = RefEdit5.Value
        arr(6) = RefEdit6.Value
        arr(7) = RefEdit7.Value

        Range("A1:N6438").AutoFilter Field:=Col, Criteria1:=arr, Operator:=xlFilterValues

    Thank you again for your time, and response.

    Best.



    • Marked as answer by SteveDB1 Wednesday, September 2, 2015 7:55 PM
    • Edited by SteveDB1 Wednesday, September 2, 2015 9:16 PM addendum to solution
    Wednesday, September 2, 2015 7:30 PM

All replies

  • This syntax only allows for Criteria1 and Criteria2. There is no support for Criteria3 etc.

    Instead, set Operator to xlFilterValues, and set Criteria1 to an array that specifies the values. You have to specify a series of fixed values though, this method doesn't support wildcards. For example:

        Dim arr(1 To 4) As String
        arr(1) = "1234"
        arr(2) = "2345"
        arr(3) = "3456"
        arr(4) = "4567"
        Range("A1:N6438").AutoFilter Field:=4, Criteria1:=arr, Operator:=xlFilterValues

    If you want multiple criteria with wildcards, you have to use Advanced Filter instead of AutoFilter.

    Set up a criteria range, e.g. P1:P5.

    In P1, enter the field name (column header) of the field you want to filter on.

    In P2 to P5, enter the criteria. For example:

    ID
    *3512*
    *3514*
    *3516*
    *3518*

    Now you can use code like this:

        Range("A1:N6438").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("P1:P5")

    You can fill the criteria range programmatically of course.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SteveDB1 Wednesday, September 2, 2015 7:55 PM
    Wednesday, September 2, 2015 7:18 PM
  • Hi Hans.

    Thank you for your response.

    Ok..... I've tinkered with your code a bit, and found what I wanted. 

    Here's my modifications for a multi-filter, with 7 criteria. Apparently, turning it to an array is the trick (Thank you-- BIG TIME :-D )

        

    Public Sub CommandButton1_Click()

    Dim arr(1 To 7) As String
    Dim Col As Variant
    'Because Excel is limited to 2 criteria for filtering
    ' this code is set up to filter 7 criteria.
    ' The array elements which have no value assigned to them
    ' will show a blank cell. So, keep that in mind
    ' when seeing results.
     Col = RefEdit8.Value

        arr(1) = RefEdit1.Value
        arr(2) = RefEdit2.Value
        arr(3) = RefEdit3.Value
        arr(4) = RefEdit4.Value
        arr(5) = RefEdit5.Value
        arr(6) = RefEdit6.Value
        arr(7) = RefEdit7.Value

        Range("A1:N6438").AutoFilter Field:=Col, Criteria1:=arr, Operator:=xlFilterValues

    Thank you again for your time, and response.

    Best.



    • Marked as answer by SteveDB1 Wednesday, September 2, 2015 7:55 PM
    • Edited by SteveDB1 Wednesday, September 2, 2015 9:16 PM addendum to solution
    Wednesday, September 2, 2015 7:30 PM