Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete unnecessary Named Ranges | Excel Discussion (Misc queries) | |||
duplicate named ranges- how to detect, delete? | Excel Discussion (Misc queries) | |||
delete or edit named dell ranges | Excel Discussion (Misc queries) | |||
How do I delete all named ranges in a sheet at one time? | Excel Worksheet Functions | |||
Delete LOCAL named ranges | Excel Programming |