Pivot Table - Old remaining values for rows

[ZorionK]

Dear experts,

We are developing some reports with MS - Excel, using pivot tables.

We created a pivot table, pointing to some information in the same XL
document.
But when we delete some data, it is still in the pivot filters data.
We want a method of delete/refresh the data available for filter using
MS-Excel commands or VBA-scripts.
We don't want to recreate the pivot table.

For instance, let's imagine that we have a database with the hospitals in
the world.
We create the following pivot table rows = cities, counting the hospitals in
each city.
PivotTable
count of hospitals
Madrid - 70
London - 85
Barcelona - 54
Paris - 82
John - 1

You can imagine that we have a wrong city (because of a test error) and John
has appeared.
We delete from the source data this row (we delete John or modify it to
London), and refresh the pivot table.

PivotTable
count of hospitals
Madrid - 70
London - 86
Barcelona - 54
Paris - 82

But if we try to select the cities, we can see the city John here!!

We want to refresh this data in order to avoid John in the selection filter.
Using VBA-Script or MS-Excel-PivotTable functionalities.

Thanks in advance.

HOW TO REPRODUCE THE ERROR:
If you want to reproduce the issue, you can run the following script (Create
a new workbook, create a module, paste the macro routine and execute it).
You will see that there isn't data for City=John, but if you try to filter
London, you will see that John is also available.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/04/2009 by zorion
'

'
Sheets("Sheet3").Select
ActiveCell.FormulaR1C1 = "City"
Range("B1").Select
ActiveCell.FormulaR1C1 = "NumHospitals"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Barcelona"
Range("B2").Select
ActiveCell.FormulaR1C1 = "54"
Range("A3").Select
ActiveCell.FormulaR1C1 = "London"
Range("B3").Select
ActiveCell.FormulaR1C1 = "85"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Madrid"
Range("B4").Select
ActiveCell.FormulaR1C1 = "70"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Paris"
Range("B5").Select
ActiveCell.FormulaR1C1 = "82"
Range("A6").Select
ActiveCell.FormulaR1C1 = "John"
Range("B6").Select
ActiveCell.FormulaR1C1 = "1"
Range("B7").Select
Sheets("Sheet2").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet3!A:B").CreatePivotTable TableDestination:="Sheet2!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="City"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("NumHospitals")
.Orientation = xlDataField
.Caption = "Sum of NumHospitals"
.Function = xlSum
End With
Sheets("Sheet3").Select
Range("A6").Select
ActiveCell.FormulaR1C1 = "London"
Range("B6").Select
Sheets("Sheet2").Select
Range("A7").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

 

[Tom Hutchins]

You could use a macro like the following:

Sub DelPivottableItem()
Dim WhichFld As String, WhichItm As String
WhichFld = InputBox("Which field has the item to be deleted?")
If Len(WhichFld) = 0 Then Exit Sub
WhichItm = InputBox("Which item should be deleted?")
If Len(WhichItm) = 0 Then Exit Sub
With ActiveSheet.PivotTables("PivotTable1").PivotFields(WhichFld)
.PivotItems(WhichItm).Delete
End With
End Sub

Hope this helps,

Hutch

 

[Ron Coderre]

Here are a couple options:

Debra Dalgleish's website covers this topic:
http://www.contextures.com/xlPivot04.html

Also at that site is the PivotPlayPlus add-in
that contains a handful of utilities for
managing pivot tables and query tables:
http://www.contextures.com/xlPivotPlayPLUS01.html

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[ZorionK]

Thanks a lot!

The script found in your first link seems to fully solve our issue:

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

Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub

Best regards
Zorion