PDA

View Full Version : Pivot Table Show All



icu222much
07-15-2010, 08:58 AM
How would I reset a pivot table to that it shows all of the items without using loops? Currently, I am using a loop to go through each list and select all the items. This however takes an extremely long time. I have tried using .showAllItems = True but it doesn't seem to work. Any advice?

geekgirlau
07-15-2010, 09:28 PM
You're right, ShowAllItems = useless! I believe 2007 has a ClearAllFilters option for the pivot field, but for 2003 try this


Dim pvt As PivotTable
Dim pvtFld As PivotField
Dim pvtItm As PivotItem

Application.Calculation = xlCalculationManual
On Error Resume Next

Set pvt = ActiveSheet.PivotTables("pvtControlBox")
pvt.ManualUpdate = True

For Each pvtFld In pvt.PivotFields
' doesn't work!!!
pvtFld.ShowAllItems = True

For Each pvtItm In pvtFld.PivotItems
pvtItm.Visible = True
Next pvtItm
Next pvtFld

pvt.ManualUpdate = False
Application.Calculation = xlCalculationAutomatic

icu222much
07-16-2010, 09:05 AM
Thank you for your reply GeekGirlAu. I ran your code but it still look a very long time to run as I have lots of items in a few of my fields. I think the issue is becuase it is a loop within a loop. Is there a way where I can just select the "Show All" item instead of going through each item and manually selecting it?

Oh yes, and I am running Office 2003.

geekgirlau
07-19-2010, 04:48 PM
This is the only method I could find. Setting ManualUpdate to True improved the performance somewhat, but only to a degree.

icu222much
07-19-2010, 07:20 PM
I tested my code with and without manual update. They both took ~3 min give or take a second.

Thank you though :)