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

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

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

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