Solved: pivot table problem

[Ger]

I have a problem with deleted data in a pivot table. In the pulldown menu deleted data is still selectabel. How do i delete these data?

Ger

[Paul_Hossler]

Try

[VBA]
PivotTables(1).PivotCache.MissingItemsLimit = xlMissingItemsNone
[/VBA]

Paul

[JWhite]

Paul has the right answer but there is a "gotcha" associated with using that option. I changed all the pivottables in my applications to "xlmissingitemsnone" and it worked just like I wanted. It got rid of all those pesky pivotitems which were showing up from old data but didn't exist in the new data.

Then I ran into a problem. Let's say one of your columns is "Region" and it is either North, South, East, or West. And let's say a particular pivottable only wants to display South or East items. So you uncheck North and West. Easy.

The problem occurs if you select "xlmissingitemsnone" and one day there are no rows from the "West" region. The report still works fine since you didn't want West rows to show and there aren't any in the data. But the NEXT time you run the report the West items will now be visible and your report is messed up. Why? Because Excel "forgets" that you had unchecked West since there was no data for it last time and it doesn't carry over any items that were missing.

My application has lots of reports from one set of data and they often require un-checking some pivotitems which don't necessarily exist in every set of data. So I had to go back to the default of letting it carry over missing items so it would remember what was unchecked even if the data wasn't there. That may not be true for your application but since I had to discover this the hard way I thought I'd pass it along.

[Ger]

Thanks both,

I just want to run it once. If i don't uncheck values it may work.
Paul, can you give me the complete vba lines and the place where to put it.

Thx

Ger

[Paul_Hossler]

Well, I have a PT 'Toolkit' with PT related items, and mine is in my .Refresh sub


[VBA]
Sub PT_Refresh(P As PivotTable)
On Error Resume Next
With P
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
.RefreshTable
End With
End Sub
[/VBA]

Paul

[Ger]

Thx


Ger