More than 2 contains filters using autofilter in VBA

Topic

New Reply

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

Reply | Quote

Replies

Use code to loop through the data building up a list of valid actual values, then use that array in the autofilter.

Reply | Quote

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.

Reply | Quote

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.[/quote]

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.

Reply | Quote

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 :o::D

Reply | Quote

Hi Andrew

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

zeddy

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote