Obsolete items in pivot table list

In a pivot table of mine, when I go to filter the data using the Row Label, where it shows the checkbox list where you can select one or many or all items to be included, this list includes items that no longer exist. Or alternatively, if you go to the PivotTable Field List and select the field and try to filter there, the same thing shows up.

That is, I used to have a certain item in that column in my Excel spreadsheet (the source for the pivot table) and a month ago I stopped using that certain item, so it no longer appears at all in the data source. But, it still shows up in the checkbox list for the Row Label in the pivot table. How can I remove these? Refreshing the pivot table does not fix this. 开发者_StackOverflow There are already a lot of different boxes and this just makes it harder to read.

Thanks for any help

---

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

Clear Old Items from a Pivot Table in Excel 2007

1. Right-click a cell in the pivot table
2. Click on PivotTable options
3. Click on the Data tab
4. In the Retain Items section, select None from the drop down list
5. Click OK, then refresh the pivot table

---

If this is something you frequently encounter in the pivot table consider creating a VBA routine to remove old items.

1. Press Alt-F11 to access the VBA editor
2. In the project explorer window double click "ThisWorkbook"
3. In the top two dropdowns above the code window select "Workbook" on the left and "Open" on the right.

4. Paste the following code from adapted from Excel Pivot Table Tutorial -- Clear Old Items

   Private Sub Workbook_Open()
   
       'prevents unused items in non-OLAP PivotTables
       'pivot table tutorial by contextures.com
       Dim pt As PivotTable
       Dim ws As Worksheet
       Dim pc As PivotCache
   
       'change the settings
       For Each ws In ActiveWorkbook.Worksheets
         For Each pt In ws.PivotTables
           pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
         Next pt
       Next ws
   
       'refresh all the pivot caches
       For Each pc In ActiveWorkbook.PivotCaches
         On Error Resume Next
         pc.Refresh
       Next pc
   
   End Sub     
   

This will remove any old items every time the workbook is opened, assuming macros are enabled.