Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Many PivotItems' Visible property to False
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Many PivotItems' Visible property to False
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. -- Charles Chickering "A good example is twice the value of good advice." "Greg Lovern" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlling Pivotitems.visible | Excel Discussion (Misc queries) | |||
Trouble setting visible property for PivotItems | Excel Programming | |||
Error 1004 with PivotItems.Visible | Excel Programming | |||
Visible PivotItems matching criteria | Excel Programming | |||
Always keep the same 4 PivotItems visible | Excel Programming |