From a previous post by Debra Dalgleish

Message-ID:
Date: Fri, 22 Aug 2003 17:54:18 -0400
From: Debra Dalgleish
Subject: Pivot table filter list doesn't refresh
Newsgroups: microsoft.public.excel.programming



To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

For earlier versions, you can run the following macro:
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
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.VisibleFields
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
'================================

--
Regards,
Tom Ogilvy


"Steven Revell" wrote in message
...
Hi All,

I was wondering how to update a pivot table so that any
deleted data does not appear. The refresh command will add
any data not already in the table but it doesn't remove
data.

Any ideas?

Thanks,
Steven