Hi All,

I was wondering how to update a pivot table so that any
deleted data does not appear. The refresh command will add
any data not already in the table but it doesn't remove
data.

Any ideas?

Thanks,
Steven

From a previous post by Debra Dalgleish

Message-ID:
Date: Fri, 22 Aug 2003 17:54:18 -0400
From: Debra Dalgleish
Subject: Pivot table filter list doesn't refresh
Newsgroups: microsoft.public.excel.programming



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

--
Regards,
Tom Ogilvy


"Steven Revell" wrote in message
...
Hi All,

I was wondering how to update a pivot table so that any
deleted data does not appear. The refresh command will add
any data not already in the table but it doesn't remove
data.

Any ideas?

Thanks,
Steven

Cheers Tom,

I'm running Office 97 and the code says its deleting the pivot items but
it doesn't seem to have any effect on the pivot table.

Cheers,

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Code should work in xl97. I assume you are deleting data in the source
table?

--
Regards,
Tom Ogilvy


"Steven Revell" wrote in message
...
Cheers Tom,

I'm running Office 97 and the code says its deleting the pivot items but
it doesn't seem to have any effect on the pivot table.

Cheers,

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'm have deleted all the data in the source table and it still doesn't
seem to work. i took out the "on error resume next" and it just errors
everytime it tries to delete an item.

i'm really not sure why its not working, the sheet isn't protected.

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This is some code from Stephen Bullen - it is essentially the same, but give
it a whirl: (it does exclude DATA, and doesn't have on error resume next -
so maybe that will make a difference).


Sub RemoveOldLabels()
Dim oPiv As PivotTable
Dim oField As PivotField
Dim oItem As PivotItem
Set oPiv = ActiveSheet.PivotTables(1)
For Each oField In oPiv.PivotFields
If oField.Name < "Data" Then
For Each oItem In oField.PivotItems
If oItem.RecordCount = 0 And _
Not oItem.IsCalculated Then _
oItem.Delete
Next
End If
Next
End Sub


--
Regards,
Tom Ogilvy

"Steven Revell" wrote in message
...
I'm have deleted all the data in the source table and it still doesn't
seem to work. i took out the "on error resume next" and it just errors
everytime it tries to delete an item.

i'm really not sure why its not working, the sheet isn't protected.

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'm have deleted all the data in the source table and it still doesn't
seem to work. i took out the "on error resume next" and it just errors
everytime it tries to delete an item.

i'm really not sure why its not working, the sheet isn't protected.

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!