Deleting Range Names in Excel, Pivot table values 1

[Gavona]

1)When one deletes ranges containing named ranges the range names are still stored in the sreadsheet, usually with "REF!" somewhere within the range definition.

I guess that there may be other legacies hidden within well used spreadsheets that have a very long life and maybe I should really try to create them anew every so often but it isn't always possible or easy.

Does anyone have a bit of code that could clean up my rangenames and any other suggestions for sorting similar problems?

2)I have some pivot tables that I re-use each month - changing the source data range to point at my latest data. I have noticed however that the pivot table stores every value that has ever existed (this becomes obvious if one goes into the field settings and try to hide particular data values). It would be relatively easy to re-work things so I create a new pivot table each time but again if their is a way to clean up the original it would make life easier.

Thanks,

Gavin

 

[xlbo]

2) If you are using anything later than xl97, you should have a "pivotcache" property of the pivot table - clear this and old values will disappear. If not, you're probably stuck with re-creating the pivot table

1)Try this out
Sub delREFNames()
With ActiveWorkbook
For Each rn In .Names
If InStr(1, rn.Value, &quot;REF&quot <> 0 Then
rn.Delete
Else
End If
Next
End With
End Sub Rgds
~Geoff~

 

[Gavona]

1) Brilliant . One day I hope to find such solutions easy!
2) I have Excel 2000, VB 6.0 but can't find that proprty in help. Maybe I am stumbling around in the dark.. Given that I can't find it then I can't work out how to clear it. I tried:
ActiveWorkbook.PivotCaches.....various
Worksheets(&quot;NurSumINs&quot.PivotTables(1).PivotCache.Clear

but not suprisingly had no joy.

Can you please tell me:
a) how I find out about this property and
b) also give me a line or two of code that should clear the cache (e.g.of any pivot tables on the current sheet) - this will at least tell me if my version of excel supports the functionality.

 

[xlbo]

Unfortunately, I have xl97 so I don't have that property either but have seen it discussed many times w/r to this kind of problem

If you go to the VBE and press F2 to get the object model up
you should then be able to find PivotTable by scrolling down to &quot;P&quot;

On the right hand side, you should then see all the properties associated with a pivot table - pivotcache should be listed here if available - also, if you go to help and type &quot;pivotcache&quot; it should give you results Rgds
~Geoff~

 

[Gavona]

Thanks Geoff,

You have helped re-assure me that I was looking in the right places. I can find PivotCache and identify how to refresh it (which seems to be the same as refreshing a pivot table manually). However I can't find a way of clearing the cache or otherwise getting read of values that used to exist as per my original problem.

I'll just have to re-creating the tables each month (although this has its own problems) - unless someone else out their knows how to solve my problem.

Gavin

 

[xlbo]

Hi - lobbed pivotcache into google and got a link to MSDN web knowledge base. from there:
PivotCache Method

Returns a PivotCache object that represents the cache for the specified PivotTable report. Read-only.

expression.PivotCache

expression Required. An expression that returns a PivotTable object.

Example
This example causes the PivotTable cache for the first PivotTable report on worksheet one to be optimized when it’s constructed.

Worksheets(1).PivotTables(&quot;Pivot1&quot _
.PivotCache.OptimizeCache = True


Within the object model, once you have got the pivotcache up, you should be able to see all properties / methods of the pivotcache - one of 'em should be CLEAR (hopefully) Rgds
~Geoff~

 

[Gavona]

Thanks, you have been really helpful. I have looked through all the methods and properties of Pivotcache and can't find anything to help, unfortunately.

Thanks again,

Gavin

 

[ChadElzinga]

I've fount it! I've been struggling with this problem all day, and I've found that you can set the PivotCache.MissingItemsLimit to 0 and that will get rid of all the old values in the PivotField objects. Whew! That's a load off. The old values were causing one of my macros to crash. Hope you find this helpful.

Chad

 

[Gavona]

Well done. Unfortunately it appears that this solution is only available from Excel 2002....