Sub to delete only all damaged defined ranges in book
 

How could the sub below be amended so that
it deletes only the defined ranges which are damaged/destroyed, eg:
MyT =#REF!$A$1:$A$6

while leaving valid ones intact, eg:
MyT2 =Sheet2!$A$1:$A$3

Currently it removes everything. Thanks.

Sub DeleteNames()
Dim nm As Object
For Each nm In ActiveWorkbook.Names
nm.Delete
Next
End Sub

Sub to delete only all damaged defined ranges in book
 

Hi max

if you want to check for #REF you could do following:

For Each nm In ActiveWorkbook.Names
Debug.Print nm.Name
If InStr(1, nm, "#REF") Then nm.Delete
Next nm

hth

Carlo

On Jan 8, 10:37*am, "Max" wrote:
How could the sub below be amended so that
it deletes only the defined ranges which are damaged/destroyed, eg:
MyT =#REF!$A$1:$A$6

while leaving valid ones intact, eg:
MyT2 =Sheet2!$A$1:$A$3

Currently it removes everything. Thanks.

Sub DeleteNames()
Dim nm As Object
* * For Each nm In ActiveWorkbook.Names
* * * * nm.Delete
* * Next
End Sub

Sub to delete only all damaged defined ranges in book
 

Thanks, Carlo! That works well.