Pivot Table - Junk in fields

[Minh Phan]

Hi,

I am reusing my pivot tables by updating the data sheet
that supports them. This data typically includes customers
who may or may not be on the list during each update.

The issue I am having is that customers who are no longer
on the current list are still showing in the pivot fields
(the drop down ones with the selection box's in the "row"
area).

Does anyone know how I can get rid of this?

Big, BIG, thanks to anyone who can help.

Minh

 

[Richard Choate]

Are you refreshing the table?
Richard Choate

Hi,

I am reusing my pivot tables by updating the data sheet
that supports them. This data typically includes customers
who may or may not be on the list during each update.

The issue I am having is that customers who are no longer
on the current list are still showing in the pivot fields
(the drop down ones with the selection box's in the "row"
area).

Does anyone know how I can get rid of this?

Big, BIG, thanks to anyone who can help.

Minh

 

[Minh Phan]

Yes. The pivot table has been refreshed and the data is
good. I get the new customers and everything. It's the old
customers who are on the previous list who are still
showing up even though I have deleted them. There is no
data associated with these old customers. The only place
these lingering customers show up is in the drop down
field I mentioned.

 

[Richard Choate]

Are there headings for these customers with no data? Do there names appear
anywhere on the data page?

Yes. The pivot table has been refreshed and the data is
good. I get the new customers and everything. It's the old
customers who are on the previous list who are still
showing up even though I have deleted them. There is no
data associated with these old customers. The only place
these lingering customers show up is in the drop down
field I mentioned.

 

[Debra Dalgleish]

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
'=============================

For earlier versions, 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.PivotFields
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
'========================

 

[Molly]

This can be done strictly using Code?

Having issues with this myslef and would prefer not to
put code in the spreadsheet in question.

Thanks.

 

[Debra Dalgleish]

You can try it with VisibleFields only, and it should run slightly
faster. However, unless you can upgrade to Excel 2002, where this issue
has been solved, there will be delays as items are deleted:

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

 

[Debra Dalgleish]

You can store the code in another workbook, e.g. Personal.xls, and run
it from there.

 

[Minh Phan]

Thank you very much. You have been a big help!

Minh