Pivot Table Data Refresh Problem

[christinemiller]

Help..when I delete and item from my data set and refresh
my pivot tables the data is missing but it's still listed
in the drop down box. How do I refresh the drop down box
for my rows of data?

 

[Ken Wright]

From Debra Dalgleish:-

Sub PivotTableRefresh()
'Debra Dalgleish
'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

 

[Debra Dalgleish]

There are instructions on the following page for removing the old items
manually, or programmatically (code for Excel 2002 and for earlier
versions):

http://www.contextures.com/xlPivot04.html