Hello all,

I will appreciate advice on how to update pivot table
items in Excel 2002. My pivot table is based on the named
range, which I am updating from time to time using
RefreshTable method. The problem is that while the pivot
table updates data correctly, it does not update pivot
items, showing me totally irrelevant list of values. For
example, if I downsize the table from 10 records to 1, it
still shows me all 10 items in the field filter. It does
not make any sense; is it a Microsoft bug, or am I missing
something?

Please help!
Andrei

There are instructions here for clearing old items from the dropdown lists:

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

RADO wrote:
I will appreciate advice on how to update pivot table
items in Excel 2002. My pivot table is based on the named
range, which I am updating from time to time using
RefreshTable method. The problem is that while the pivot
table updates data correctly, it does not update pivot
items, showing me totally irrelevant list of values. For
example, if I downsize the table from 10 records to 1, it
still shows me all 10 items in the field filter. It does
not make any sense; is it a Microsoft bug, or am I missing
something?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

It is more a philosophical difference of opinion. The MS view is that if
the items existed once, then they are still possible item values and just
have no records associated with them at this time. Anyway, previously

Posted by Debra Dalgleish:

http://groups.google.com/groups?selm...ontextures.com

To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

For earlier versions, you can run the following macro:
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
'================================

--------------------------

I don't have excel 2002 available to me, but I suspect you might be able to
set the missing items limit property some where manually - although this is
just a guess.

--
Regards,
Tom Ogilvy



"RADO" wrote in message
...
Hello all,

I will appreciate advice on how to update pivot table
items in Excel 2002. My pivot table is based on the named
range, which I am updating from time to time using
RefreshTable method. The problem is that while the pivot
table updates data correctly, it does not update pivot
items, showing me totally irrelevant list of values. For
example, if I downsize the table from 10 records to 1, it
still shows me all 10 items in the field filter. It does
not make any sense; is it a Microsoft bug, or am I missing
something?

Please help!
Andrei

Tom, thanks a lot! I solved the problem with your help.

Regarding the philisophy - thanks, I understand now, although it's very
conter-intuitive. If I were MS, I would at least change the default
option to xlMissingItemsNone, because my guess is that this is the vast
majory of cases in practice.

Best -
Andrei





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Debra - thank you very much, it helped! I appreciate your advice.

best regards -
Andrei



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!