LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 15th 04, 03:55 PM posted to microsoft.public.excel.programming
external usenet poster
First recorded activity by ExcelBanter: Dec 2003
Posts: 5
Default 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
pTable.ManualUpdate = False
'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 ?


"If I have not seen as far as others, it is because giants were
standing on my shoulders."
-- Hal Abelson

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot table from different workbooks BENONG New Users to Excel 0 August 11th 10 05:54 AM
The Incredible Shrinking Chart LarryP Excel Discussion (Misc queries) 1 March 29th 10 11:02 PM
Pivot Table from 2 different workbooks Eqa Excel Discussion (Misc queries) 0 March 30th 07 06:38 AM
Pivot Table from 2 different workbooks Eqa Excel Discussion (Misc queries) 0 March 30th 07 06:30 AM
How do I join two workbooks into one pivot table? Eqa Excel Worksheet Functions 0 March 29th 07 07:54 AM

All times are GMT +1. The time now is 11:22 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"


Copyright © 2017