Clearing out old Dates in a Pivot Table

estrauss22

New Member
Joined
Jul 5, 2007
Messages
32
I have a pivot table built for "x" number of days. The days change as the year goes along. The data feeding the pivot table changes and has only the most current dates. However, wheneve I refresh the pivot table, every date I've ever had there is displayed. Is there a way to clear out old dates without recreating the entire pivot table?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Try this code.
Code:
Sub Clean_Pivots()
    For Each pt In ActiveSheet.PivotTables
     pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next
End Sub
...Why do old descriptions of OLD data hang around?
Because they're in the domain of PivotField. They're retained so a fixed-dimension PivotTable (with "Show items with no data" checked) can be created; thus, preventing a pre-configured GETPIVOTDATA function from returning #REF! when the data changes.

Above gleaned from this thread:
http://www.mrexcel.com/board2/viewtopic.php?p=1078163
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Sorry, here are some instructions.

To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macro code will start with "Sub MacroName()" and End with "End Sub"

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialogue box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.
 

estrauss22

New Member
Joined
Jul 5, 2007
Messages
32
This code has worked from in the past but now when I run it, it brings back the error "Syntax Error" and highlights the following line:

*pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Any input on how to fix?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top