P
Paul Keenan
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
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