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

方法：1、首先按下Alt+F11键，出现如何窗口2、点击插入，再点击模块，出现如下窗口：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 0End Sub