Hi Akyhne,

Just to add (and to complete my abnegation!), try running Peter's demo
'Test' macro, which defines various ranges and then intentionally breaks
some of the names.

Then run my macro and look at the Insert | Names | Define dialog.

Now run Peter's suggested routine and, again, check the Insert | Names |
Define dialog.


Does that clarify matters!


---
Regards,
Norman



"akyhne" wrote in message
...
Well, some of my names ARE referring to multi-area ranges, but still they
are
deleted correctly.

"Peter T" skrev:

Similar to Norman's but with InStr in case broken name refers to a
multi-area range -

Sub DelNames()
Dim n As Long
Dim nm As Name
Dim vArr()
n = 1
For Each nm In ActiveWorkbook.Names
If InStr(nm.RefersTo, "#REF!") Then
n = n + 1
ReDim Preserve vArr(1 To n)
vArr(n) = nm.Name
nm.Delete
End If
Next
If n 1 Then
vArr(1) = "Names deleted"
ActiveWorkbook.Worksheets.Add
Range("A1").Resize(n, 1).Value = Application.Transpose(vArr)
MsgBox "Find & rectify these names if used in formulas"
Else
MsgBox "No names deleted"
End If

End Sub

Sub Test()
With ActiveWorkbook
For i = 1 To 4
.Names.Add "myNameACE_" & i, Union([a1], [c1], [e1])
.Names.Add "myNameAE_" & i, Union([a1], [e1])
.Names.Add "myNameAC_" & i, Union([a1], [c1])
Next
Columns("C:C").Delete
End With

' DelNames
End Sub

Better still, use the NameManager addin which you can get from the
authors'
sites of Jan Karel Pieterse and Charles Williams:

www.jkp-ads.com
www.DecisionModels.com

Regards,
Peter T

"akyhne" wrote in message
...
How to delete broken named ranges in sheet?

When you manually delete rows or columns than contains named ranges,
the
named ranges in the deleted area is not deleted, but remains with a
faulty
reference as (Example): MYNAMEDRANGE =SHEETS!#REFERENCE!