Hi Ayhne,


> Well, some of my names ARE referring to multi-area ranges, but still they
> are
> deleted correctly.

You experience will be true *if*, and only if, the last area in the
multi-area range has been deleted.

If the last area in the multi-area range remains intact, the solution
proposed by me will fail.

Peter's suggestion, however, uses the InStr function to search for the
"#REF!" string irrespective of where it occurs in the name's address string
and is, therefore, independent of which area, or areas may be missing.

Dump my suggestion and go with Peter's.


---
Regards,
Norman



"akyhne" <akyhne@discussions.microsoft.com> wrote in message
news:0C07B849-8DDD-4528-9C09-5460F6F51A3B@microsoft.com...
> 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" <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!
>>
>>
>>