Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: Removing old field values from Pivot Table

    How do you remove old field values from the Pivot Table drop downs for a field?

    Usually, I can do this by removing the field from the pivot table, refreshing and adding the field back in but it doesn't always work and can become time consuming.

    Example,

    Field1
    Value1
    Value2
    Value3
    Value4

    Value3 is no longer used but still shows in the drop down for Field1

  2. #2
    Join Date
    Oct 2007
    Posts
    127
    have you worked it out... im having the same problem

  3. #3
    Join Date
    Jan 2006
    Posts
    47

    Still trying...

    I have not found a solution to this problem yet.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    That unfortunately is a characteristic of Pivot tables. Nothing can be done, except recreate the Pivot table from scratch.

    If this becomes a regular/daily problem, then it might be worth checking into VBA code to construct the Pivot when needed, which avoids this problem. And VBA code with Pivots, if designed correctly, can be quite fast.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2007
    Posts
    127
    Thanks....

    Creating it in VBA coding would be the solution...

    How do I go about creating it in VBA...

    Thanks in advance

  6. #6
    Join Date
    Jan 2009
    Posts
    1

    Removing old field values from pivot table

    Hi,

    You can use below code for removing old field values from pivot table

    Sub refpivot()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim ptTable As PivotTable
    Dim ptField As PivotField
    Dim ptItem As PivotItem
    Dim i As Long

    Set wbBook = ActiveWorkbook

    On Error Resume Next

    For i = 1 To 2
    For Each wsSheet In wbBook.Worksheets
    For Each ptTable In wsSheet.PivotTables
    For Each ptField In ptTable.PivotFields
    For Each ptItem In ptField.PivotItems
    ptItem.Delete
    Next ptItem
    Next ptField
    ptTable.RefreshTable
    Next ptTable
    Next wsSheet
    Next i

    On Error GoTo 0
    End Sub

  7. #7
    Join Date
    Feb 2009
    Posts
    3
    A field will basically act as a filter for your data.


    For each field that you want to use in order to filter your data, you can specify exactly which items to include.


    The page field can only have one field associated with it.


    This is not actually part of the wizard steps ?


    Changing the layout of the Pivot Table is not an actual step in the Wizard as it can be done either before or after the pivot table report has been created.


    1) During the Pivot Table Wizard, pressing the Layout button on Step 3.


    2) Using the PivotTable Field List Pane.


    3) Dragging the fields once the pivot table has been created.

  8. #8
    Join Date
    Oct 2012
    Posts
    1
    This will keep the values updated to only what is in the data set

    Right-click a cell in the pivot table
    Click on PivotTable options
    Click on the Data tab
    In the Retain Items section, select None from the drop down list.
    Click OK, then refresh the pivot table.

Posting Permissions

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