How to reset pivot table filter field box contents

Hi all,

I have a pivot table which reads from a fairly large list of entries (about 17000 records). The table was tedious to build, so I typically update the data, then refesh the table to produce the new results. The issue I have is that the pivot table field filter dropdown boxes tend to accumulate obsolete data over time and I cannot figure out how to reset these so that only the current values are shown.

For instance, I have a field "Prog" which lists various programs in progress. When I created the table, Prog1, Prog2, and Prog3 existed, and the pivot filter dropdown listed all three. Over time, some of these programs completed, and the records were deleted so data no longer include Prog1 and Prog2. New programs Prog4 and Prog5 were also added. However, the field filter list box shows all 5. (While this is not a big deal with 5 projects, it is wihen the list climbs to 50.)

If anyone can tell me how to reset this without completely rebuilding the pivot table, I'd appreciate it. I'm using Excel 2003 SP3 and Windows XP.

---GJC

Hi gjc

I had a similar situation recently and found the answer on the Contextures website, see link below.

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

I opted for the VBA solution, which works fine but it has a quirk in that the 'blank' option in the dropdown list is no longer at the end of the list.

Would be interested to know why that is but it can be lived with as is.

Hope this helps ...spellbound

Thanks, Spellbound.

The manual solution on the referenced website reordered but did not eliminate the obsolete items. The VBA solution, however, worked fine for me (and "blank" remained at the end of the list in my case...)

I really appreciate the tip, this will save me a lot of time!

---GJC

Glad I could help

Just tried again with the same macro in my workbook. No old items to remove but still leaves the 'Blank' option not at the bottom in some fields.

Strange because I am also running Excel 2003 but with SP2.

As I said before, it is something I can live with.

spellbound