Pivot Table Data-Deleted Data still appears

[Guest]

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

 

[Gary Brown]

Try this link

http://www.contextures.com/xlPivot04.html

 

[Guest]

Thanks.

CrimsonPlague29

 

[Guest]

THanks - I've trying to overcome this for quite a while!

 

[Debra Dalgleish]

There are instructions here for clearing old items from a pivot table:

http://www.contextures.com/xlPivot04.html

 

[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

 

[HKaplan]

One quick fix would be to add this statement to your macro code:

Application.ScreenUpdating = False
Your code
Your code
Your code
Application.ScreenUpdating = True


This freezes the screen and causes the macro to run generally much
faster.

 

[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