+ Reply to Thread
Results 1 to 3 of 3

Pivot Table Autofilters Not Clearing On Refresh

  1. #1
    Registered User
    Join Date

    Pivot Table Autofilters Not Clearing On Refresh


    I have an application which presents an interface to users so they can select parameters for data retrieval. When they are satisfied with the parameters they have entered they then click a button which will cause data to be retrieved and displayed in an Excel app. Just for reference this is a production reconciliation report.

    The flow is:
    1) User requests report by entering in parms, clicking button.

    2) Data is retrieved

    3) Excel reconciliationMaster.xls file is copied to recon_<yyyy-dd-mm hh:mm:ss>.xls. (the master file template for all reports)

    ---- the copied file is used from this point on ----

    4) The program creates an Excel app

    5) The sheet which holds source data for pivot tables is cleared and repopulated with the newly retrieved data and the data range is redefined.

    6) The program walks through each pivot table in the file and performs a refresh.

    The problem is:
    The autofilters behave oddly. If I take an item and move it into the Page Field area, the data in the autofilter looks fine. If I take the same item and move it into the Column Fields area, the autofilter contains entries for the current data as well as any data which was contained in the master file. The data itself looks fine, it just looks like the autofilters are not being cleared for the Column Fields when a refresh is performed.

    I have googled and wandered through the object models (which I will continue to do), but I cannot see how to programmatically force a clear of the autofilter information for the Column Fields for a set of pivot tables.

    Any ideas?



  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365

    Pivot Table Autofilters Not Clearing On Refresh

    If I understand you correctly, the Pivot Table dropdowns contain obsolete items from previous refreshes.

    You have a couple options:
    1) Some time ago, I wrote PivotPlay, a free add-in that works on Pivot Tables. One of its features is that it clears obsolete items with the click of a button.
    It's available here:

    2) See Debra Dalgleish's coverage of your issue and a few options on handling it.

    Does either of those help?
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date

    Thank you. The clearing of the PivotCache.MissingItems was what I needed. Ignorance is not bliss.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1