Sub to delete only all damaged defined ranges in book

[Max]

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

 

[carlo]

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

 

[Max]

Thanks, Carlo! That works well.