清除数据透视表“垃圾”的代码;垃圾:数据源里已经被删除的部分,透视表里依然显示存在 把下段代码放入ThisWorkbook文件中,运行代码 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
[此贴子已经被作者于2010-9-28 17:34:44编辑过] |