Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
range names to cell references
I have inherited a spreadsheet with over 5000 range names. Unfortunately,
these range names were not created intuitively and have little meaning to what they refer to. I would like to have a version of this workbook with only the cell range references in the formulas for ease of navigation. Is there some fast way to convert all range names to cell references? I thank you in advance for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
range names to cell references
Chris,
The macro below will work with one major caveat: each name needs to be completely unique: if you have one name that forms part of another name, then it may cause errors. For example, if you have ChrisName, ChrisName1, and ChrisName2, then it won't work, because the string "ChrisName" appears in its entirety in the next two names . But if you have just ChrisName1, and ChrisName2, then it will work. Try it on a copy of your workbook... HTH, Bernie MS Excel MVP Sub RemoveNameReferences() Dim myN As Name Dim mySht As Worksheet Dim myNstr As String For Each mySht In ActiveWorkbook.Worksheets For Each myN In ActiveWorkbook.Names mySht.Cells.Replace What:=myN.Name, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN For Each myN In mySht.Names mySht.Cells.Replace What:=myN.Name, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN For Each myN In mySht.Names myNstr = Replace(myN.Name, IIf(InStr(1, mySht.Name, " ") 0, _ "'" & mySht.Name & "'!", mySht.Name & "!"), "") MsgBox myNstr mySht.Cells.Replace What:=myNstr, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN Next mySht End Sub "Chris" wrote in message ... I have inherited a spreadsheet with over 5000 range names. Unfortunately, these range names were not created intuitively and have little meaning to what they refer to. I would like to have a version of this workbook with only the cell range references in the formulas for ease of navigation. Is there some fast way to convert all range names to cell references? I thank you in advance for your help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
range names to cell references
I should have noted that this will work with multiple identical sheet level names.
HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Chris, The macro below will work with one major caveat: each name needs to be completely unique: if you have one name that forms part of another name, then it may cause errors. For example, if you have ChrisName, ChrisName1, and ChrisName2, then it won't work, because the string "ChrisName" appears in its entirety in the next two names . But if you have just ChrisName1, and ChrisName2, then it will work. Try it on a copy of your workbook... HTH, Bernie MS Excel MVP Sub RemoveNameReferences() Dim myN As Name Dim mySht As Worksheet Dim myNstr As String For Each mySht In ActiveWorkbook.Worksheets For Each myN In ActiveWorkbook.Names mySht.Cells.Replace What:=myN.Name, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN For Each myN In mySht.Names mySht.Cells.Replace What:=myN.Name, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN For Each myN In mySht.Names myNstr = Replace(myN.Name, IIf(InStr(1, mySht.Name, " ") 0, _ "'" & mySht.Name & "'!", mySht.Name & "!"), "") MsgBox myNstr mySht.Cells.Replace What:=myNstr, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN Next mySht End Sub "Chris" wrote in message ... I have inherited a spreadsheet with over 5000 range names. Unfortunately, these range names were not created intuitively and have little meaning to what they refer to. I would like to have a version of this workbook with only the cell range references in the formulas for ease of navigation. Is there some fast way to convert all range names to cell references? I thank you in advance for your help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
range names to cell references
Bernie.
Thanks for your rapid response, but this didn't work. I should've pointed out that the formulas in the cells were sums; i.e. sum(chris1). Also, upon inspection, I'm seeing that a few of the range names refer to other workbooks. Thanks. Chris -- Chris "Bernie Deitrick" wrote: Chris, The macro below will work with one major caveat: each name needs to be completely unique: if you have one name that forms part of another name, then it may cause errors. For example, if you have ChrisName, ChrisName1, and ChrisName2, then it won't work, because the string "ChrisName" appears in its entirety in the next two names . But if you have just ChrisName1, and ChrisName2, then it will work. Try it on a copy of your workbook... HTH, Bernie MS Excel MVP Sub RemoveNameReferences() Dim myN As Name Dim mySht As Worksheet Dim myNstr As String For Each mySht In ActiveWorkbook.Worksheets For Each myN In ActiveWorkbook.Names mySht.Cells.Replace What:=myN.Name, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN For Each myN In mySht.Names mySht.Cells.Replace What:=myN.Name, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN For Each myN In mySht.Names myNstr = Replace(myN.Name, IIf(InStr(1, mySht.Name, " ") 0, _ "'" & mySht.Name & "'!", mySht.Name & "!"), "") MsgBox myNstr mySht.Cells.Replace What:=myNstr, _ Replacement:=Mid(myN.RefersTo, 2), _ LookAt:=xlPart Next myN Next mySht End Sub "Chris" wrote in message ... I have inherited a spreadsheet with over 5000 range names. Unfortunately, these range names were not created intuitively and have little meaning to what they refer to. I would like to have a version of this workbook with only the cell range references in the formulas for ease of navigation. Is there some fast way to convert all range names to cell references? I thank you in advance for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Names convert to Cell References | Excel Discussion (Misc queries) | |||
Cell References/Names | Excel Discussion (Misc queries) | |||
Help, change range names back to cell references? | New Users to Excel | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Replace range names with cell references? | Excel Worksheet Functions |