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

方法：

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

3、将以下代码复制并粘贴进去，按下F5键运行即可清除。

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