Autofilter range (table) field with multiple criteria 'contains'

  • Hi all,


    I already designed a macro in which i create a vba array of filters for a field (something like arrayFilters=Array("apple", "banana", "tomato")) and then, with

    Code
    1. TABLERANGE.AutoFilter Field:=filtercolumn, Criteria1:=arrayFilters, Operator:=xlFilterValues

    i manage to filter the specified column by the exact values in the array.


    However, what i want to achieve is the same only that instead of filtering by the values that match exactly each filter condition, doing by 'contains' condition (currently trying to do so by creating an array like .arrayFilters=Array("*apple*", "*banana*", "*tomato*")), but no luck so far.


    I have read threades on advanced filtering, but for that i need to provide a range for criteriarange parameter, and i want all this process to go behind the scenes, so to speak (currently the user only has to write the filtering conditions on top of each header column separated by comas (such as: apple, banana, tomato) and then the macro should do the rest (i.e. loop through each column header and create an array of the filters to apply to that particular field).


    Any light on this??


    Thanks a lot in advance!

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    You won't be able to do that many multi-contains using just AutoFilter. But, I think you're on the right track with ADvFilter. You could have your filter take the users input, split it on commas, and write to some cells on a hidden worksheet. Have that hidden worksheet setup to be your criteria range of an AdvFilter, and you're good to go. See the attached for an example. Try typing something like
    Apple,Blue into A1, and you'll see the filter work.

    Files

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    Thanks a lot for the quick reply Luke. However, the reason i didnt want to used advanced filter was precisely the thing about having to store my filters in a hidden range/sheet just for this purpose, specially cause the macro is designed to be stored in my personal macro workbook and, thus, should be working regardless the workbook (and i think it is too much effort to add 1 sheet and then deleting it behind the scenes just to filter a table).


    I'll try to find a workaround or just assume the impossibility! Thanks a lot again!


    PS: how do i upvote ur reply? as it is deffinitely the only way to solve the pb...

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    Your personal workbook is allowed to have it's own worksheets that you can reference, if that helps. Do let us know if you find a workaround.


    As for upvoting, I don't think this forum support that type of marking. Would be nice...

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    You can alternatively loop through the values to see if they contain the data you want, and build up an array of exact matching values that you can then apply as an autofilter.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    Rory.. is this what you mean..?



    I guess you could also build the new array 1 by 1 using Redim Preserve and a counter to set the Ubound value..

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    That principle, yes, though I'd use named args and enums rather than positional and literals, and better variable names. ;)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi Apo,

    this is is excactly what I need - but I have a silly question: My row which needs to be searched is not column A (column 1) it is column AI (column 36) how I have to amend your macro to get this work?

  • Re: Autofilter range (table) field with multiple criteria 'contains'


    You won't be able to do that many multi-contains using just AutoFilter. But, I think you're on the right track with ADvFilter. You could have your filter take the users input, split it on commas, and write to some cells on a hidden worksheet. Have that hidden worksheet setup to be your criteria range of an AdvFilter, and you're good to go. See the attached for an example. Try typing something like
    Apple,Blue into A1, and you'll see the filter work.

    Hi Luke,

    if my column which needs to be searched is not column A (it is Column AI) so what I have to amend in your code to get it work?


    Thanks so much for your reply!

  • Hi Luke,

    if my column which needs to be searched is not column A (it is Column AI) so what I have to amend in your code to get it work?


    Thanks so much for your reply!

    Don't need to amend the code, just change some things in the workbook. Unhide Sheet2, and change the value of cell A1 to be whatever header you want to do the filter on. The other part you may need to change is in the Formulas - Name Manager, make sure the definition of rngTable is the correct range of your table.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."