Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table cruft and incredible shrinking workbooks
Hey you guys,
I was finding that refreshing the same pivot table over and over left a lot of old items in the drop-down lists. I wrote the following code to get rid of these eyesores, but was surprised to find how much space was saved in the resultant workbooks. The VB code essentially iterates through all of the pivot items in all the pivot tables in a workbook, and tries to delete them all. Excel will either carry out the deletion, or will throw an error if the item has a genuine reason for its own existence. The 'On Error Resume Next' directive ensures such errors are ignored. -------------------snippety snip-------------------------- Public Sub RemovePivotCruft() 'Operates on all pivot tables in the active workbook. 'Tries to delete all the items; Excel prevents items with 'related data from being deleted. '(c) Paul J. Keenan, 2004 Dim wSheet As Worksheet Dim pTable As PivotTable Dim pField As PivotField Dim pItem As PivotItem On Error Resume Next For Each wSheet In ActiveWorkbook.Worksheets For Each pTable In ActiveSheet.PivotTables pTable.ManualUpdate = True For Each pField In pTable.PivotFields For Each pItem In pField.PivotItems pItem.Delete Next Next pTable.ManualUpdate = False Next Next 'Set .Saved property to false otherwise Excel will not prompt 'you to save changes ! ActiveWorkbook.Saved = False End Sub -------------------snippety snip-------------------------- I was more surprised, possibly even perplexed, to discover the same macro can be run on newly-created pivot tables (e.g. brand-new pivot generated by VB from an Access query) and the space savings keep rolling in. I had put the space savings down to removing redundant data items from the pivot cache, but surely with a new pivot, there shouldn't be any savings to make ? A 50MB spreadsheet regularly turns into a 15MB with no loss of data or functionality that I can see. Can anyone explain the Excel behaviour here ? Is there a downside ? Cheers. -- pjk "If I have not seen as far as others, it is because giants were standing on my shoulders." -- Hal Abelson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table from different workbooks | New Users to Excel | |||
The Incredible Shrinking Chart | Excel Discussion (Misc queries) | |||
Pivot Table from 2 different workbooks | Excel Discussion (Misc queries) | |||
Pivot Table from 2 different workbooks | Excel Discussion (Misc queries) | |||
How do I join two workbooks into one pivot table? | Excel Worksheet Functions |