Autofilter for a text that contains "Hit" (in an array)

Hi All,

Im having an issue writing a macro to run an autofilter for multpile criteria, with one of those criteria defined as part of the text string contains Hit.
What ive come up with so far is this:

Please Login or Register  to view this content.

Now when I record a macro for a custom filter for a text that contains Hit, I get this:

Please Login or Register  to view this content.

So when I replace

Please Login or Register  to view this content.

in the first macro with

Please Login or Register  to view this content.

from the second macro I no longer get any results for "Hit" or "Hit Face"
What gives?

Any help would be greatly appreciated, thanks!

Originally Posted by Izandol

You may not use wildcards with an array - it must be exact matches only.

Hello Izandol, thanks for the reply!
That explains the mystery, definitely good to know.
Do you know of a way around this?
Thanks!

Originally Posted by Norie

Why not use a second criteria for contains 'Hit'?

Hello Norie, thanks for the reply!
Is that refferring to the first or second macro?
Thanks!

Originally Posted by Norie

It would be a combination of the two.

Thank Norie, when I tried this:

Please Login or Register  to view this content.

the filter still didnt return any text strings that contains "Hit", only the exact matches. What am I doing wrong?

Thanks!

Originally Posted by Izandol

You may not use array filter and regular filter on the same field (operator must be xlFilterValues so you may not use xlOr).

Please Login or Register  to view this content.

Something along these lines maybe? This doesnt run but am I headed in the right direction? Thanks!

Originally Posted by Norie

Pierce

That won't work.

Try filtering for the array then filtering for contains 'Hit'.

Norie,
When I try this

Please Login or Register  to view this content.

none of the values in the array are displayed, but the "Hit" values are.
When I do this:

Please Login or Register  to view this content.

none of the "Hit" values are displayed, but the array values are.

This is frustrating

Originally Posted by Izandol

You may not achieve this with autofilter unless you provide all exact values, or make an additional formula column which can calculate your criteria. Perhaps an advanced filter will be better.

Thanks Izandol, I'll be recording a macro using the advanced filter shortly to see what I can dissect.

What happens if you use the And operator?

Please Login or Register  to view this content.

Originally Posted by Norie

Pierce

Apologies, Izandol is right.

I could have sworn I'd previously applied similar filter criteria using AutoFilter.

Thanks Noire, I 'll be attempting to record the advanced filter shortly.

Ok so after some research and then recording I came up with this:

Please Login or Register  to view this content.

I placed the criteria list in M1:M5 with the same header as the column I want filtered, I I used the wildcard *Hit* along with the other values.

It appears to be working initally, but i'll be testing it for a while yet.

So I ended up using the code for the advanced filter and everything is working as planned.
Marking this as solved, thanks!