[Ken Roberts]

I have created a pivot table in which I am frequently
adding and deleting information from the pivot data
source. After refreshing the table, the updates are
reflected. HOWEVER, if the field I am working with is
part of the LAYOUT area (Row or column) and I choose to
select only some of the data from the field, it shows a
list of all data that has ever been a part of the data.
So, even though for example, I deleted the name Jon Doe
from my pivot data source field called 'Names', it remains
one of the 'Names' that I can choose to display. Of
course, if I do choose Jon Doe, the name is not shown in
the report and no data for him is shown either.
Ironically, if I Pivot Drag the field to the PAGE area, my
choices are '(All)' or any of the names that have data.
Jon Doe will not even be a choice.

IS THERE A WAY TO REVISE THIS? IN OTHER WORDS, I WOULD
LIKE IT IF WHEN I HIT REFRESH THAT THE CHOICES FOR 'NAMES'
FROM THE LAYOUT AREA DOES NOT REFLECT NAMES THAT HAVE
SINCE BEEN REMOVED FROM MY PIVOT DATA. CAN THIS BEEN
DONE? HOW?

Please help!

Ken Roberts
617 951 7494

[Tom Ogilvy]

Previously posted 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
"Ken Roberts" wrote in message
...
I have created a pivot table in which I am frequently
adding and deleting information from the pivot data
source. After refreshing the table, the updates are
reflected. HOWEVER, if the field I am working with is
part of the LAYOUT area (Row or column) and I choose to
select only some of the data from the field, it shows a
list of all data that has ever been a part of the data.
So, even though for example, I deleted the name Jon Doe
from my pivot data source field called 'Names', it remains
one of the 'Names' that I can choose to display. Of
course, if I do choose Jon Doe, the name is not shown in
the report and no data for him is shown either.
Ironically, if I Pivot Drag the field to the PAGE area, my
choices are '(All)' or any of the names that have data.
Jon Doe will not even be a choice.

IS THERE A WAY TO REVISE THIS? IN OTHER WORDS, I WOULD
LIKE IT IF WHEN I HIT REFRESH THAT THE CHOICES FOR 'NAMES'
FROM THE LAYOUT AREA DOES NOT REFLECT NAMES THAT HAVE
SINCE BEEN REMOVED FROM MY PIVOT DATA. CAN THIS BEEN
DONE? HOW?

Please help!

Ken Roberts
617 951 7494

[Ken Roberts]

Tom:

I consider myself an advanced user but I have no idea how
to implement your suggestion. Furthermore, will this
automatically do this for all of my existing files with
pivots or do I have to do it every time I open the file?

Please let me know the next 'easy' steps.

- Ken Roberts


-----Original Message-----
Previously posted 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
"Ken Roberts" wrote in message
...
I have created a pivot table in which I am frequently
adding and deleting information from the pivot data
source. After refreshing the table, the updates are
reflected. HOWEVER, if the field I am working with is
part of the LAYOUT area (Row or column) and I choose to
select only some of the data from the field, it shows a
list of all data that has ever been a part of the data.
So, even though for example, I deleted the name Jon Doe
from my pivot data source field called 'Names', it
remains
one of the 'Names' that I can choose to display. Of
course, if I do choose Jon Doe, the name is not shown in
the report and no data for him is shown either.
Ironically, if I Pivot Drag the field to the PAGE area,
my
choices are '(All)' or any of the names that have data.
Jon Doe will not even be a choice.

IS THERE A WAY TO REVISE THIS? IN OTHER WORDS, I WOULD
LIKE IT IF WHEN I HIT REFRESH THAT THE CHOICES
FOR 'NAMES'
FROM THE LAYOUT AREA DOES NOT REFLECT NAMES THAT HAVE
SINCE BEEN REMOVED FROM MY PIVOT DATA. CAN THIS BEEN
DONE? HOW?

Please help!

Ken Roberts
617 951 7494


.

[Tom Ogilvy]

You put the code in a general module in the VBE.

You have to actively run it (Tools=Macros). As written, it runs against
all pivot tables in a specific workbook. Run it when you need it.

Not sure why you are posting the question in programming if you don't know
anything about programming? (although in xl2000 and earlier, I don't think
there are other options).

--
Regards,
Tom Ogilvy


Ken Roberts wrote in message
...
Tom:

I consider myself an advanced user but I have no idea how
to implement your suggestion. Furthermore, will this
automatically do this for all of my existing files with
pivots or do I have to do it every time I open the file?

Please let me know the next 'easy' steps.

- Ken Roberts


-----Original Message-----
Previously posted 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
"Ken Roberts" wrote in message
...
I have created a pivot table in which I am frequently
adding and deleting information from the pivot data
source. After refreshing the table, the updates are
reflected. HOWEVER, if the field I am working with is
part of the LAYOUT area (Row or column) and I choose to
select only some of the data from the field, it shows a
list of all data that has ever been a part of the data.
So, even though for example, I deleted the name Jon Doe
from my pivot data source field called 'Names', it
remains
one of the 'Names' that I can choose to display. Of
course, if I do choose Jon Doe, the name is not shown in
the report and no data for him is shown either.
Ironically, if I Pivot Drag the field to the PAGE area,
my
choices are '(All)' or any of the names that have data.
Jon Doe will not even be a choice.

IS THERE A WAY TO REVISE THIS? IN OTHER WORDS, I WOULD
LIKE IT IF WHEN I HIT REFRESH THAT THE CHOICES
FOR 'NAMES'
FROM THE LAYOUT AREA DOES NOT REFLECT NAMES THAT HAVE
SINCE BEEN REMOVED FROM MY PIVOT DATA. CAN THIS BEEN
DONE? HOW?

Please help!

Ken Roberts
617 951 7494


.