Removing named ranges

[K. Georgiadis]

Is it possible to accomplish the following with macros:


Remove all named ranges in the workbook
Remove only those named ranges that contain a #REF! error (because referenced cells have been deleted)

[mdmackillop]

1.

Sub DelAllRanges()
Dim r As Name
For Each r In ActiveWorkbook.Names
r.Delete
Next
End Sub

2.

Sub DelRefRanges()
Dim r As Name
For Each r In ActiveWorkbook.Names
If InStr(1, r, "#REF!") > 0 Then r.Delete
Next
End Sub

[K. Georgiadis]

Fantastic! I have an immediate opportunity to test #1! Thanks.

[Cyberdude]

Sub DelAllRanges()
For Each r In ActiveWorkbook.Names
r.Delete
Next
End Sub Malcolm, how would you Dim "r" in your sub?

[mdmackillop]

Hi Sid,
It should be declared as Name. I've added it to the code.

[Bob Phillips]

There are various system defined names which you should leave alone.

The ones that I know of are of the form


*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria

Best to test for these and not delete them.