list of values include old non valid data

  • Thread starter Thread starter zeyad alhekail
  • Start date Start date
Z

zeyad alhekail

I create a pivot table by connecting to a table on SQL
server DB. it works OK , say that we have a product
number in the table. when we click on the list of values
it show the available values ( like a filter ). say the
available values are 1,2,3 & 4

I change the data source and update , it work OK. except
it show the old available product in the list along with
the new ones. it only show it in the drop down list

any hints ?
 
To clear the list manually, drag the field out of the pivot table,
refresh the pivot table, and drag the field back to the pivot table.

To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

If you have an earlier version, you can run the following macro:
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
'================================
 
Back
Top