清除数据透视表下拉列表里显示无用的数据项

方法：

1、首先按下Alt+F11键，出现如何窗口

2、点击插入，再点击模块，出现如下窗口：

Sub ClearMissingItems()

'清除数据透视表下拉列表里显示无用的数据项

Dim pvt As PivotTable, pvtcache As PivotCache

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets

For Each pvt In sht.PivotTables

pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Next pvt

Next sht

On Error Resume Next

For Each pvtcache In ActiveWorkbook.PivotCaches

pvtcache.Refresh

Next pvtcache

On Error GoTo 0

End Sub