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:=xlDatab ase, 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