Unable to set the visible property of the PivotItem class

I read that I can have this error if there is no other pivot item visible, but if I try to make visible all the items and then hide the ones I don't want to show it still crashes:

Please Login or Register  to view this content.

On the other hand, if I use the recorded version it works:

Please Login or Register  to view this content.

This is so weird... :S

I have read in MSDN that it's a bug in MS Excel. You can't change the visibility of two contiguous pivot items.

The solution might be to go through them jumping a pivot item at a time in two loops.

I'll give it a try.

Finally I could solve it looping through the Pivot field twice with the following code:

Please Login or Register  to view this content.

It isn't very effficient, but it avoids the problem of contiguous pivot items.

No way, it still displays the error.

How can MS doesn't solve this bug? It's been there for a long, long time...

I had to use the following code, but it depends on public variables and in case of a crash of the excel workbook I was screwed...

Please Login or Register  to view this content.

I used previousfilter as a global variable to store the filter I must remove, but in the end i transformed it into an array that stores all the active filters and then I hide them individually.

It is sad to have to use this tricks to do such a simple operation:

Please Login or Register  to view this content.

Hope this is useful for someone else.

Quite sure.

Now I have found the error. It's a problem of making visible the elements in the first place, and then hide the ones Ia m interested. the error comes when you try to hide the only item that is visible.

So the code ends up like this:

Please Login or Register  to view this content.

Now if you excuse me, I am going to bump my head against the table as a punishment for my clumsiness...

I read so much about the contiguous issue I gave for certain the problem might be there.

Originally Posted by OnErrorGoto0

So you were trying to hide all of them (albeit not on purpose)...

Well... I must say in my defense that it was only sometimes... when the filter to show was tha bottom of the Pivot field... :P

Thanks for the assistance.

Ugh. Literally 6 hours of coding/searching/reformatting solved by that one rearrangement.
Thank you.

Nice.

Great to hear that.