|
|
|
|
|
|
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()
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
For Each ws In ThisWorkbook.Worksheets
For Each pvtTable In ws.PivotTables
pvtTable.RefreshTable
For Each pvtField In pvtTable.PivotFields
For Each pvtItem In pvtField.PivotItems
pvtItem.Delete
Next
Next
Next
Next
Application.ScreenUpdating = True
End Sub
|
How to use:
|
- In excel, press Alt+F11 to open the VBE
- Right-Click on VBAProject(YourWorkbookName) and click on Insert --> Module
- Copy and paste this code in the new module
- Go back to your excel workbook (or close the VBE)
|
Test the code:
|
- Download the sample file.
- Click on the filter drop-down to view available items to filter. Make a mental note.
- Remove one of the items from the original data.
- Refresh the PivotTable
- 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.
- Now let's delete that item.
- Press Alt+F8 and select "DeleteUnusablePivotItems"
- Press "Run"
- 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.
|
|