Debra Dalgleish's clearing old items from Pivot Table dropdowns
 

Hi.

I was hoping to direct this question directly to Debra as she wrote the code
but if anyone else can assist feel free to reply.


I used the code on http://www.contextures.com/xlPivot04.html (I'm using
Excel 2000) to clear old items from the drop-downs. It took over 30 minutes
to complete.

On completion I clicked on a drop-down and Excel shut down - frustrating!!
I repeated this procedure, saved the file on completion, closed the file and
then tried to re-open the file. Again the file caused Excel to shut-down,
this time before the file opened.

Have you ever experienced this?

I also tried the manual way suggested. Although it didn't crash it didn't
get rid of the old items.

I should also point out there are approximately 10 worksheets within the
workbook, with the majority haveing a pivot table on them. I took your (and
others) advice and used the same data to use less data. Does this create a
problem?

Any suggestions would be greatly appreciated.




--
Andrew

Debra Dalgleish's clearing old items from Pivot Table dropdowns
 

The code loops through all the pivot items in each visible pivot fields,
so it will be slow if there are many pivot items/fields.
For pivot tables based on the same pivot cache, you could just run the
code on one pivot table, and all the connected pivot tables would be
updated.
For the manual method to work, you'd have to remove the field from all
copies of the pivot table that share the same pivot cache, then refresh
the pivot table.

'====================
Sub DeleteOldItems_PT()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
pt.RefreshTable
pt.ManualUpdate = True
For Each pf In pt.VisibleFields
If pf.Name < "Data" Then
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next pi
End If
Next pf
pt.ManualUpdate = False
pt.RefreshTable
Set pt = Nothing
End Sub
'============================

Andrew wrote:
Hi.

I was hoping to direct this question directly to Debra as she wrote the code
but if anyone else can assist feel free to reply.


I used the code on http://www.contextures.com/xlPivot04.html (I'm using
Excel 2000) to clear old items from the drop-downs. It took over 30 minutes
to complete.

On completion I clicked on a drop-down and Excel shut down - frustrating!!
I repeated this procedure, saved the file on completion, closed the file and
then tried to re-open the file. Again the file caused Excel to shut-down,
this time before the file opened.

Have you ever experienced this?

I also tried the manual way suggested. Although it didn't crash it didn't
get rid of the old items.

I should also point out there are approximately 10 worksheets within the
workbook, with the majority haveing a pivot table on them. I took your (and
others) advice and used the same data to use less data. Does this create a
problem?

Any suggestions would be greatly appreciated.






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra Dalgleish's clearing old items from Pivot Table dropdown
 

Hi Debra,

Thanks for the reply.

I'm running your code now but it is now 2 hours into it and still going. I
break into the code (using ESC) every 15 min or so and you can see it is
progressing but gee it takes a long time.

Can you please advise why values in the drop-downs are kept. It seems such
a logical requirement yet it isn't a feature. The cache must be taking up
space in memory, affecting performance.

BTW your name is very represented on sites regarding pivot tables. Keep up
the good work.

Andrew 220208



--
Andrew


"Debra Dalgleish" wrote:

The code loops through all the pivot items in each visible pivot fields,
so it will be slow if there are many pivot items/fields.
For pivot tables based on the same pivot cache, you could just run the
code on one pivot table, and all the connected pivot tables would be
updated.
For the manual method to work, you'd have to remove the field from all
copies of the pivot table that share the same pivot cache, then refresh
the pivot table.

'====================
Sub DeleteOldItems_PT()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
pt.RefreshTable
pt.ManualUpdate = True
For Each pf In pt.VisibleFields
If pf.Name < "Data" Then
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next pi
End If
Next pf
pt.ManualUpdate = False
pt.RefreshTable
Set pt = Nothing
End Sub
'============================

Andrew wrote:
Hi.

I was hoping to direct this question directly to Debra as she wrote the code
but if anyone else can assist feel free to reply.


I used the code on http://www.contextures.com/xlPivot04.html (I'm using
Excel 2000) to clear old items from the drop-downs. It took over 30 minutes
to complete.

On completion I clicked on a drop-down and Excel shut down - frustrating!!
I repeated this procedure, saved the file on completion, closed the file and
then tried to re-open the file. Again the file caused Excel to shut-down,
this time before the file opened.

Have you ever experienced this?

I also tried the manual way suggested. Although it didn't crash it didn't
get rid of the old items.

I should also point out there are approximately 10 worksheets within the
workbook, with the majority haveing a pivot table on them. I took your (and
others) advice and used the same data to use less data. Does this create a
problem?

Any suggestions would be greatly appreciated.






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra Dalgleish's clearing old items from Pivot Table dropdown
 

Perhaps the old items were kept so the user could see them, even if they
weren't in the current data.
If you enable the 'Show items with no data' option, you could quickly
see if any items were not in the new data. This might alert you to a
problem with a new download.

In Excel 2002, and later versions, you can set the number of missing
items that should be retained.

Andrew wrote:
Hi Debra,

Thanks for the reply.

I'm running your code now but it is now 2 hours into it and still going. I
break into the code (using ESC) every 15 min or so and you can see it is
progressing but gee it takes a long time.

Can you please advise why values in the drop-downs are kept. It seems such
a logical requirement yet it isn't a feature. The cache must be taking up
space in memory, affecting performance.

BTW your name is very represented on sites regarding pivot tables. Keep up
the good work.

Andrew 220208





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html