Delete broken named ranges in sheet

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

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

If so probably because the "last" area in your multi-area name is #REF!.

Insert the following new line in DelNames()

If InStr(nm.RefersTo, "#REF!") Then
Debug.Print nm.Name, nm.RefersTo ' new line

Run my Test() example at look at the Immediate Window, Ctrl-G.
The names "myName_AC* would have been deleted with both methods (Right &
InStr). But the myName_ACE* names only with InStr.

Regards,
Peter T

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

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