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

have you worked it out... im having the same problem

I have not found a solution to this problem yet.

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.

Thanks....

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

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

Thanks in advance

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

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.