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!

Delete broken named ranges in sheet
 

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

--

HTH

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


"akyhne" wrote in message
...
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!

Delete broken named ranges in sheet
 

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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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
...
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" wrote in message
...
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!

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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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" wrote in message
...
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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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" wrote in message
...
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" wrote in message
...
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!

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" wrote in message
...
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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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

Delete broken named ranges in sheet
 

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" wrote in message
...
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" wrote in message
...
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!

Delete broken named ranges in sheet
 

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" wrote in message
...
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

Delete broken named ranges in sheet
 

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" wrote in message
...
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