Pivot with Filter by Multiple Contains?

[uberathlete]

Aaack this is driving me crazy! Is there any way to do a pivot filter with multiple contains?

Say I want to filter pivot field "Type" to make visible the pivot item if it contains the word "type1" OR "type2" . Is there a code that can achieve this?

I normally use this code to filter by specific pivot items:

Please Login or Register  to view this content.

But this only works if I the pivot item is exactly type1 or type2. But if a pivot item like "bla bla type1" exists and I want that visible, I'd have to list that in the above code as well. So contains is a more appropriate filter but I am having a really hard time finding a code that allows for multiple contains.

Any help would be very much appreciated.



Edit: I have attached a file with data and desired result.

[Richard Buttrey]

Hi,

Do you really need a macro to manipulate a pivot table? Why can't you just use the normal filtering options?

[uberathlete]

Afraid not because there can be hundreds of pivot items that contain the words "type1" and "type2", and well ... my boss asked for it, lol. So I'm in quite a bind.

[oeldere]

show a small excel example and the desired result.

[uberathlete]

Hi oeldere. I have attached a file in my opening post.

[oeldere]

Please Login or Register  to view this content.

basicly you have another question.

how you extract text out of a cell. e.g. type1.

If you are able to achieve that, you can use that formula in column C (helpcolumn) and use that column in the pivot table.

[MarvinP]

This looks like an "Instr()" type of VBA function is needed...

If INSTR(ucase(cells(4,"A")),"TYPE1") >0 then ' this will test to see if "type1" is contained in the cell A4.

I hope you can expand on this to make your code work for you.

[uberathlete]

Hmm I think I got it. This is the code I used for those intrested:

Please Login or Register  to view this content.

[MarvinP]

Good Job!!

Looks like you got it!! I'm a little worried about "Type1" vs "type1" vs "TYPE1" not being the same. That is why I did an UCase in my example.

[uberathlete]

Thanks MarvinP. Your suggestion made all the difference. With regards to case sensitivity, I believe vbtextcompare ignores case, so it would treat Type1, type1, and TYPE1 as the same.