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

mamercio · Mar 24th 2015

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

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!

**Luke M** · Mar 24th 2015

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.

mamercio · Mar 25th 2015

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...

**Luke M** · Mar 25th 2015

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...

**rory** · Mar 25th 2015

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.

apo · Mar 25th 2015

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

Rory.. is this what you mean..?

Code

Private Sub CommandButton1_Click()
    Dim filarr, z, zz, x, i As Long
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        x = Application.Transpose(.Offset(1).Resize(.Rows.Count - 1).Value)
        filarr = [{"Apple", "Banana", "tomato"}]
        For i = LBound(filarr) To UBound(filarr)
            z = z & vbLf & Join(Filter(x, filarr(i), , vbTextCompare), vbLf)
        Next i
        zz = Split(z, vbLf)
        .AutoFilter 1, zz, 7
    End With
End Sub

Display More

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

**rory** · Mar 25th 2015

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.

Conexus_One · Nov 14th 2021

Quote from apo

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

Rory.. is this what you mean..?

Code

Private Sub CommandButton1_Click()
    Dim filarr, z, zz, x, i As Long
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        x = Application.Transpose(.Offset(1).Resize(.Rows.Count - 1).Value)
        filarr = [{"Apple", "Banana", "tomato"}]
        For i = LBound(filarr) To UBound(filarr)
            z = z & vbLf & Join(Filter(x, filarr(i), , vbTextCompare), vbLf)
        Next i
        zz = Split(z, vbLf)
        .AutoFilter 1, zz, 7
    End With
End Sub

Display More

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

Display More

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?

Conexus_One · Nov 14th 2021

Quote from Luke M

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!

**Luke M** · Nov 18th 2021

Quote from Conexus_One

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.

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

Participate now!

Share