O
olivier durand
Hi,
That's a tricky one, so seat down, take a deep breath and enjoy...
I've been using the following code successfully to delete old items
from the Pivot Tables I use.
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
....this has never been a problem until I decided to optimise the size
of my excel file by following the method below:
http://groups.google.com/groups?q=a...&[email protected]&rnum=4
(basicallly this sets all the table to point to the same cache and
then disable cache saving).
The problem I encounter now is as follows:
- excel tries to delete items which are actually existing (i.e. where
pi.recordcount >0 !!!). The Macro crashes and excel too! Even more
irritable is that the error is random (or at least tend to allow me to
perform the operation once and then crashes afterward)
So I have tried the following solutions/workarounds:
- re-enable cache saving (enabling table option "Save data with table
layout")
-> no success
- Steping through the code
-> works great! (no so practical though, especially if you don't have
access to the code)
- save workbook, Restart Excel2000/win2000
-> no success
- included wait state in the code
-> no success
Could that be a pivot cache corruption problem?...
Good luck to you all and thanks in advance!
PS: I could send you a sample of my file if you were interested.
That's a tricky one, so seat down, take a deep breath and enjoy...
I've been using the following code successfully to delete old items
from the Pivot Tables I use.
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
....this has never been a problem until I decided to optimise the size
of my excel file by following the method below:
http://groups.google.com/groups?q=a...&[email protected]&rnum=4
(basicallly this sets all the table to point to the same cache and
then disable cache saving).
The problem I encounter now is as follows:
- excel tries to delete items which are actually existing (i.e. where
pi.recordcount >0 !!!). The Macro crashes and excel too! Even more
irritable is that the error is random (or at least tend to allow me to
perform the operation once and then crashes afterward)
So I have tried the following solutions/workarounds:
- re-enable cache saving (enabling table option "Save data with table
layout")
-> no success
- Steping through the code
-> works great! (no so practical though, especially if you don't have
access to the code)
- save workbook, Restart Excel2000/win2000
-> no success
- included wait state in the code
-> no success
Could that be a pivot cache corruption problem?...
Good luck to you all and thanks in advance!
PS: I could send you a sample of my file if you were interested.
