Delete broken named ranges in sheet

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!

There is no automatic way, you need to re-instate the name.

--

HTH

RP
(remove nothere from the email address if mailing direct)


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

Try:

Sub DeleteBrokenNames()
Dim Nme As Name

For Each Nme In ActiveWorkbook.Names
If Right(Nme.RefersTo, 5) = "#REF!" Then
Nme.Delete
End If
Next Nme

End Sub



---
Regards,
Norman



"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 Norman
Your code seams to do the job. Thanks!

"Norman Jones" skrev:

> Hi Akyhne.
>
> Try:
>
> Sub DeleteBrokenNames()
> Dim Nme As Name
>
> For Each Nme In ActiveWorkbook.Names
> If Right(Nme.RefersTo, 5) = "#REF!" Then
> Nme.Delete
> End If
> Next Nme
>
> End Sub
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "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!
>
>
>

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

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

If you work with names, do yourself a favor and get a copy of Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

akyhne wrote:
>
> 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!

--

Dave Peterson

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

Hi Dave,

Actually Name Manager two good plugs earlier in the thread. But it deserves
at least three in any topic relating to names <g>

Regards,
Peter T

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43009446.4F6DB140@verizonXSPAM.net...
> If you work with names, do yourself a favor and get a copy of Jan Karel
> Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
>
> You can find it at:
> NameManager.Zip from http://www.oaltd.co.uk/mvp
>
> akyhne wrote:
> >
> > 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!
>
> --
>
> Dave Peterson

But it was way at the bottom of your post!

Am I expected to scroll down, too???

<VBG>

Peter T wrote:
>
> Hi Dave,
>
> Actually Name Manager two good plugs earlier in the thread. But it deserves
> at least three in any topic relating to names <g>
>
> Regards,
> Peter T
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43009446.4F6DB140@verizonXSPAM.net...
> > If you work with names, do yourself a favor and get a copy of Jan Karel
> > Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
> >
> > You can find it at:
> > NameManager.Zip from http://www.oaltd.co.uk/mvp
> >
> > akyhne wrote:
> > >
> > > 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!
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson