Hi Peter,

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

Very good point. Thank you!


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

I certainly endorse the reference. An invaluable addin.


---
Regards,
Norman



"Peter T" <peter_t@discussions> wrote in message
news:uU$$VKYoFHA.3552@TK2MSFTNGP10.phx.gbl...
> 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" <akyhne@discussions.microsoft.com> wrote in message
> news:04CCDFF5-CD0E-4A10-9229-ADC4C3307834@microsoft.com...
>> 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!
>
>