locked
multi-filter RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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.  

    TYIA.

    Best.

    Wednesday, September 2, 2015 3:37 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I posted this too on the Excel for Developers forum, and one of the posters responded.

     

    Here's my modifications for a multi-filter, with 7 criteria. Apparently, turning it to an array is the trick 

    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

    This appears to array through a single criteria, resulting in what I want. Furthermore, I've added an element which will allow the user to define which column they wish to filter. 

    Thus, this item is solved. 

    I'd created a user form, input 7 refedit boxes, and as you can see, assigned each to an array element. It works exactly as I wanted. Whether I use 2 or all 7. Oh, be forewarned, if you leave the refedit boxes empty, empty valued cells will be returned.



    • Marked as answer by SteveDB1 Wednesday, September 2, 2015 8:01 PM
    • Edited by SteveDB1 Wednesday, September 2, 2015 9:14 PM Addendum to solution.
    Wednesday, September 2, 2015 4:10 PM