Setting Many PivotItems' Visible property to False

[Greg Lovern]

I'm trying to make a small number of PivotItems visible, in a
PivotField that has a very large number of PivotItems, while making
all the other PivotItems in that PivotField not visible. Same as
clicking on the PivotField, uncheck "All", then check the desired
PivotItems. When doing this manually, Excel does it very fast even if
there are a huge quanitity of PivotItems.

If I loop through every PivotItem in this collection, it takes a very
long time - much, much longer than setting it interactively -- even
with PivotTable.ManualUpdate = True and Application.ScreenUpdating =
False. There are about 1700 PivotItems in this PivotField. I looked
for a property or method of the PivotItems collection that might hide
them all, but I don't see one.

Since Excel does it so much faster when I do it manually than the time
it takes in code to loop through all PivotItems and set each one's
Visible property to False, obviously Excel is doing something
different.

What can I do to get the same speed, when setting ~1700 PivotItems'
Visible properties to False, and setting only a handful of those
Visible properties to True?


Thanks,

Greg

 

[Guest]

Greg, I've experienced the same issue. While I'm not aware of a way to speed
it up, I did come up with a work around. Create an extra column in your data,
then programmatically make it true or false based on whether you want it
visible or not, then add that column as a page field in your Pivot Table. I
know it is not a pretty solution but maybe it will get you by for now.