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