Pivot Tables

[Guest]

I have dynamic data that is summarized in Pivot Tables. Once refreshed the
old data is still in the dropdowns but not actually still in the data. Is
there a way to purge non-existant data from the dropdowns?
Thanks
Steve

 

[Guest]

Hi Steve,

You probably already have this one answered, but I found some code on a
website referenced from this DG that does the trick. It was found on the
following site: http://www.contextures.com/xlPivot04.html

Here it is:

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

'in Excel 2002 and later versions
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

End Sub

Just integrate it into your code and it works beautifully!

 

[Guest]

Thanks Kent. Works great

Hi Steve,

You probably already have this one answered, but I found some code on a
website referenced from this DG that does the trick. It was found on the
following site: http://www.contextures.com/xlPivot04.html

Here it is:

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

'in Excel 2002 and later versions
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

End Sub

Just integrate it into your code and it works beautifully!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098