Pivot Table Data-Deleted Data still appears

  • Thread starter CrimsonPlague29
  • Start date
C

CrimsonPlague29

Hello,

I have a primary sheet with all the data that is pulled into my pivot table,
but if I remove the data from the data range it still appears Data Field Drop
Down Menu of the pivot table, How can these sets of data be eliminated from
the pivot table with out completely recreating the table????

Thanks
 
F

FredL

Hi Debra,

I've tried this and it works...except that my macro now takes 10 minutes to
complete (normally it's like 45 sec or so): there are 13 tabs each containing
5 pivot tables. Since my primary pivot in all the tabs are copies of 1 main
pivot, how would you code it such that only that main pivot gets cleared?
I'm not concerned about the other 4 pivots in each tabs.

Thanks,
Fred
 
D

Debra Dalgleish

If you used the code for Excel 2002 and later versions
(DeleteMissingItems2002All), you only have to run it once in the
workbook. It doesn't have to be included in any code that updates the
pivot tables.
The MissingItemsLimit setting will stick, and old items will be cleared
automatically when the pivot table is refreshed.
However, to change the code so it only affects a specific pivot table,
you can use this:

Sub DeleteMissingItems_SpecificTable()
ActiveSheet.PivotTables("RegionPT").PivotCache _
.MissingItemsLimit = xlMissingItemsNone
End Sub
 
Top