Delete broken named ranges in sheet

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" <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!
>>
>>
>>

Ok, now I see what you mean. I work with a Danish Excel and thought, that
you, with multi-area, ment that the name ranges went over more cells like
this:
$A$1:$A$4

and not like this: Ark1!$A$1;Ark1!$C$1;Ark1!$E$1:$F$1 (I didn't know it was
possible). Well we all learn a little more day by day ;-)

I'll use Peters code for safety. Thank you both!!!

"Norman Jones" skrev:

> 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" <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!
> >>
> >>
> >>
>
>
>