Pivot tables preserves old field headers

[Glory]

I have several fields in a pivot table that are holding onto old information. Is there a way to get rid of them? In "Field Settings" I can hide entries, but I can't just delete the entries.

The entries are not present in the source list, and some haven't been present literally for years. What's going on?

 

[CWatts]

Right click a cell in your pivot table and go to pivot options.

on the data tab, change the retain items to "None".

that's for excel 2007, if I recall 2003 is similar.

 

[Glory]

Re: I’m using ’03, and maybe I’m being slow, but I don’t see the “Retain” option available in the “Field Settings” or the “Table Options” menus when I right click. Not even under advanced options.

 

[Glory]

I'm also curious if there's a way to sort a field list manually. Autosort ("Ascending" in "Field Settings->Advanced Options") isn't sorting the list of information correctly, and no amount of refreshing helps.

 

[Trouttrap2]

to remove the old names in your drop down, try this.

Remove the field from your pivot table, then click refresh, then drag your field back to its position.

You can sort manually. Sometimes just by click and hold and drag to a new position. There is a setting for manual or autosort. I think its under field settings, but I don't have 2003 to check right now.

 

[Glory]

Dragging the field off the table then reading it doesn’t help. Can I do it through VBA? Do the “.remove” method and the “.add” method allow you to add items to a pivot field list as well as adding objects to forms?

 

[Trouttrap2]

you can watch a video on the subject found at:
http://www.contextures.com/xlPivot04.html

To manually clear the old items from the list:

1. If you manually created any groups that include the old items, ungroup those items.
2. Drag the pivot field out of the pivot table.
3. On the Pivot toolbar, click the Refresh button
4. Drag the pivot field back to the pivot table

If that doesn't work then they have VBA to do what you need at that site too.

 

[leezhihong]

You can try two methods. First,you can try it like Trouttrap2 suggest that drag the filed away and back in the Pivot Table. Second, try it with VBA.

Sub ClearMissingItems()
    Dim Pvt As PivotTable, PvtCache As PivotCache
    Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        For Each Pvt In Sht.PivotTables
            Pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone
        Next Pvt
    Next Sht
    On Error Resume Next
    For Each PvtCache In ActiveWorkbook.PivotCaches
        PvtCache.Refresh
    Next PvtCache
    On Error GoTo 0
End Sub

 

[Glory]

Thanks to both of you. leezhihong, that's exactly what I needed.

What I'm wondering is whether it's possible to use the ".add" and ".remove" methods to specifically add and remove certain items. I guess I'll try it out now that I have a collection to work with (pivotcache).

If anyone who’s responded to this has the time, would you mind taking a look at this thread? it's a question about comparing the contents of a PivotField collection.