Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Drop Down Question (2003)

    Greetings,

    I have a question regarding the dropdown selection feature of a pivot table. The first time we create the data, and the pivot table from the data, the dropdown selection feature lists the possibilities of show all or select the ones to show. As we change the source data by adding lines or deleting lines, the Pick Lists do not seem to "clean up" as the data changes. The Pick list in the dropdown seems to retain ALL of the possible selections, even though the data is no longer in the source data.

    Is it possible to clean up the listing in the dropdown? Refresh data does not seem to do it.


    Thanks,
    Brad

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pivot Table Drop Down Question (2003)

    Try this - you need to run the CleanUpPivots macro:
    <pre>Sub CleanUpPivots()
    Dim wks As Worksheet, pt As PivotTable
    For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
    DeletePhantomPivotTableItems pt
    Next pt
    Next wks
    End Sub
    Public Sub DeletePhantomPivotTableItems(pt As PivotTable)
    Dim pi As PivotItem, pf As PivotField
    On Error Resume Next
    pt.RefreshTable
    For Each pf In pt.PivotFields
    For Each pi In pf.PivotItems
    pi.Delete
    Next pi
    Next pf
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Drop Down Question (2003)

    You'll also find sample code to remove unused pivot items in Excel -- Pivot Tables -- Clear Items.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Drop Down Question (2003)

    I'll give it a shot!


    thanks to all,

    Brad

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Drop Down Question (2003)

    I like!!!!!!!


    Thks again,

    Brad

Posting Permissions

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