PDA

View Full Version : remove Pivot lookup value



stanl
03-08-2007, 05:01 AM
I exported an Access query to Excel and built a Pivot Table from that data range. One of the column groupings was by city. I noticed that Elizabeth City was entered as Eliz. City and I changed that in the data range, then refreshed the Pivot. However, both Eliz.City and Elizabeth City now appear in the drop down selection in the Pivot. Outside of just re-exporting and starting over, can the Eliz. City (no longer valid) be removed? Stan

malik641
03-10-2007, 11:57 PM
After some digging, yes :)

Sheet1.PivotTables("PivotTable1").PivotFields("Name").PivotItems("Delete_Me").Delete
You will get an error if the PivotItem is not in the filter list or you try to delete an item that is in the table itself...like it does some kind of check if:

1. The PivotItem is in the filter list.
2. The PivotItem is in the actual table.

If 1 is false and 2 is true, the code effectively takes out the unwanted value from the filter list (as far as I just figured out :)).


Thanks for posting this question, btw. I've run into this once before, but paid no attention to it really. This is much better than re-building a pivot table :thumb thanks for giving me that little push to try!!

malik641
03-11-2007, 10:12 AM
I think I'll make this a KB :thumb

Public Sub DeleteUnusablePivotItems()
' This will delete all items that appear in
' the filter list of a pivot table, but is not
' anymore in the pivot table.
On Error Resume Next
Application.ScreenUpdating = False
Dim ws As Excel.Worksheet
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem

' First loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
' Now loop through all pivotTables
For Each pvtTable In ws.PivotTables
' Refresh the table
pvtTable.RefreshTable
' Now loop through all pivotFields
For Each pvtField In pvtTable.PivotFields
' Now loop through all items and delete
' the unnecessary ones
For Each pvtItem In pvtField.PivotItems
pvtItem.Delete
Next
Next
Next
Next
Application.ScreenUpdating = True
End Sub

I have to use On Error Resume next incase the pivotItem is actually in the pivot table.