Excel

Delete unused PivotItems from the PivotTable filter list

Ease of Use

Easy

Version tested with

2003 

Submitted by:

malik641

Description:

Loops through all PivotTables in the workbook and removes PivotItems that appear in the filter list, but are no longer in the original data. 

Discussion:

If you have a PivotTable with a FieldItem that was deleted in the original data and you refresh the PivotTable, the FieldItem will not appear in the PivotTable but it will in the PivotField filter list. You will not want to see that PivotItem in the PivotField filter list because there will be no data to show if you filter the PivotTable by that PivotField. Use this code to remove those unnecessary items. 

Code:

instructions for use 

			
Option Explicit 
 
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

How to use:

  1. In excel, press Alt+F11 to open the VBE
  2. Right-Click on VBAProject(YourWorkbookName) and click on Insert --> Module
  3. Copy and paste this code in the new module
  4. Go back to your excel workbook (or close the VBE)
 

Test the code:

  1. Download the sample file.
  2. Click on the filter drop-down to view available items to filter. Make a mental note.
  3. Remove one of the items from the original data.
  4. Refresh the PivotTable
  5. Click on the filter drop-down on the field where you removed an item. Notice the item you deleted is still in the filter list.
  6. Now let's delete that item.
  7. Press Alt+F8 and select "DeleteUnusablePivotItems"
  8. Press "Run"
  9. Click on the filter drop-down on the field where you removed an item. Notice the item is now removed from the filter list.
 

Sample File:

Delete PivotItems Example.zip 10.72KB 

Approved by mdmackillop


This entry has been viewed 185 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express