Results 1 to 10 of 10
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    More than 2 contains filters using autofilter in VBA

    OK, I am posting this, pretty sure the answer is NO
    But worth an ask.

    And I know the work around is either all coded extracts, OR advanced filters.
    And YES it ought to be in a database, but it can't be!

    So, back to the question!

    You can have auto-filter to A or B or C etc using an array and passing it to the filter

    For example, the following picks a set of selected entries off a custom form list box
    and then passes them to a string array

    Code:
            lngCT = 0
            For varI = 0 To lstDiscipline.ListCount - 1
                If lstDiscipline.Selected(varI) = True Then
                    strCritTeam(lngCT) = lstDiscipline.List(varI)
                    lngCT = lngCT + 1
                End If
            Next
           'Apply Filter to a Range
           If lngT <> 0 Then lsoData.Range.AutoFilter Field:=4, Criteria1:=strCritTeam, Operator:=xlFilterValues
    It will even work IF you use a wild card Character when building the array

    Code:
            lngCT = 0
            For varI = 0 To lstDiscipline.ListCount - 1
                If lstDiscipline.Selected(varI) = True Then
                    strCritTeam(lngCT) = "*" & lstDiscipline.List(varI) & "*"
                    lngCT = lngCT + 1
                End If
            Next
           'Apply Filter to a Range
           If lngT <> 0 Then lsoData.Range.AutoFilter Field:=4, Criteria1:=strCritTeam, Operator:=xlFilterValues
    But in case 2 it fails if there are more than 2 array entries.
    I am guessing this is because CONTAINS which is the wild card filter is a custom filter and only allows 2 criteria on a single field, which is a shame.

    So other than advanced filter, anyone any ideas?

    There could be up to 10 wild card search variants on a single field, and this applies to more than one field too, so building
    the criteria range on the fly in vba on a hidden sheet is a bit tedious.

    My alternative is to port all the data out to maybe a SQL database temporarily, do a query there and then port it back.
    BUT... I doubt they will allow that.
    It is looking like we have to use Excel.

    Ahhhhh Happy days..
    Andrew

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Use code to loop through the data building up a list of valid actual values, then use that array in the autofilter.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Andrew

    have you thought about replicating the relevant field and using the second wildcard filter on the replicated column.


    zeddy

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Be too many fields zeddy, I would have to make them up on the fly for each field
    So, if there were 10 wild card variants of 1 field I'd need an extra 4 dummy fields just for that one.
    I reckon a lot easier to use an advanced filter.
    Shame Microsoft put a daft 2 limit which they have never ever updated.
    But then again.
    It really ought to be in a database.
    No matter how often you tell people that, they still want to use spreadsheets :-(
    Andrew

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That works with distinct values, BUT, when when there are several options all embedded needing wild cards it is the same problem I think.
    Unless I have missed a bit of basic logic somewhere.

    If I am just going to loop through the data then I may as well write my own filter extract tool.

    But advanced filter does it fine.
    It is just a bit of a pain building up the filter sequences criteria for an advanced filter because IF you want A or B or C in col 1 and X or Y in column 2 you need a 6 row advanced criteria

    e.g

    Col 1 = (A or B or C) And Col 2 = (X or Y) logic since OR logic in an advanced criteria needs a separate row so you need

    Code:
    COL 1       Col 2
    ----------------
    A             X
    B             X
    C             X
    A             Y
    B             Y
    C             Y
    But it isn't that simple because they are wild card searches
    so actually the advanced filter is

    Code:
    COL 1       Col 2
    ----------------
    *A*         *X*
    *B*         *X*
    *C*         *X*
    *A*         *Y*
    *B*         *Y*
    *C*         *Y*
    Be so easy in SQL eh....

    Anyway, I have done it with advanced filter now.
    Easy except for compiling the criteria range.

    Anyone know if you can pass an array rather than a sheet range to advanced filter?

    No, don't tell me, I have done enough work today anyway.
    Andrew

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    But Andrew, in the old days, if you wanted all your employees to use Access, you would have to pay a lot more for the extra license costs. Spreadsheets were cheaper. And perhaps less training, sort of.
    ..and never mind that three out of two people don't understand fractions.

    zeddy

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    That works with distinct values, BUT, when when there are several options all embedded needing wild cards it is the same problem I think.
    Unless I have missed a bit of basic logic somewhere.
    Yes, I think you have. You want an array of distinct values - there's no point repeating any.

    If I am just going to loop through the data then I may as well write my own filter extract tool.
    You could, but autofilter is easy.

    But advanced filter does it fine.
    It is just a bit of a pain building up the filter sequences criteria for an advanced filter because IF you want A or B or C in col 1 and X or Y in column 2 you need a 6 row advanced criteria
    No you don't - you can use a two cell criteria range with a formula in it using AND and SEARCH/FIND.


    Anyone know if you can pass an array rather than a sheet range to advanced filter?
    No, you can't.

    No, don't tell me, I have done enough work today anyway.
    Oops.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think I see where you are coming from Rory.
    Sorry for my misunderstanding.
    It had been a LOOOONNG day and I was tired.

    Tell me if I am wrong......

    If going for AutoFilter method, which would be my preferred route

    Loop through all the cells in the relevant column
    and if any individual ones match the criteria
    Then add the exact cell to the array, unless it is already there, so the result will be an array of all the cells I want.

    Makes sense now I am more awake, if that is what you were saying.


    OR, for the Advanced filter solution (depending upon which route I take)

    Use a formula in the criteria row, that generates either TRUE or False for that row to match the requirement.
    So, in my case, I would be looking at an OR() with the contents being contains any of the required values.

    If my assumption is right, I agree.
    Autofilter looks much simpler.

    I will have a re-write to accommodate that method and see where it gets me.

    Thanks for feedback.
    Next time I will look at messages when I haven't been up for 19 hours
    Andrew

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Correct. Note: with the advanced filter I am talking about a two cell criteria range, not a formula for each row of the table (the advanced filter effectively does that for you).
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Yep..
    That's what I meant re Advanced.

    A criteria Calculation that returns True or False in the second Row of Criteria that covers the necessary combined condition.

    I might even play around with both ideas and see which one I like best.
    But autofilter springs to mind because it means NOT needing an addition hidden extract sheet.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •