Pivot "row hide menu" keeps having items I don't have anymore!?!?

[atarumorooka]

Hello, my excel 2003 file manages expences and has columns for date,
amount,
account and type of expence. This one for example had values like
clothes, food, toys and so on.
As I understand the pivot "row hide menu" gets items from the data
page values so I'll find correctly clothes, food, toys....and I can
select on or more of them to be displayed in my pivot.

PROBLEM: The question is...when I cancel all the expences for food I
expect to have "food" item cancelled also from the hide menu...but it
keeps staying there and that is very annoying because I suppose the
number of items will increase with the time to an amount hard to
manage.


The page field has a property to hide the items from the "hide menu"
drop down list while the row and column fields haven't.


Any idea how to hide items or update them so to automatically sync with

the values of the main sheet?


English is not my language but I hope I was clear enough to make you
understand what is my question!!!!


Thank you,
Ataru

 

[Debra Dalgleish]

As answered in .excel.misc:

In Excel 2002 and Excel 2003 you can programmatically change a setting
to prevent old items from appearing. There is sample code here:

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

 

[atarumorooka]

Debra, thank you for the help...that is great. I still wonder why that
feature (for me a problem) is still there in Excel 2003.
Hope won't be in the next release.
Ataru

 

[paul.bellrichard]

Excel keeps a cache of indexes.
To clear this cache you have a few options (solutions borrowed from
http://www.contextures.com/xlPivot04.html):

A - Delete the old pivot table and create a new one.
B - Drag the field row out of the pivot table --> Refresh --> Drag back
C - Reset the PivotTable cache (See VBA code below)


Note: The following code was lifted directly from
http://www.contextures.com/xlPivot04.html

<<<<<<<<<<<<<< Begin VBA Code >>>>>>>>>>>>>>>
Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

'in Excel 2002 and later versions
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

End Sub
<<<<<<<<<<<<<<< End VBA Code >>>>>>>>>>>>>>>>>>

--Paul

 

[atarumorooka]

Thank you Paul. It worked.
Ataru

(e-mail address removed) ha scritto: