Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 788
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 788
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Range Names convert to Cell References AlanC Excel Discussion (Misc queries) 10 September 21st 07 12:24 AM
Cell References/Names MonkeyHanger Excel Discussion (Misc queries) 1 March 28th 07 10:00 AM
Help, change range names back to cell references? James New Users to Excel 6 November 29th 06 05:14 PM
How to rename references from range names to cell references Abbas Excel Discussion (Misc queries) 1 May 24th 06 06:18 PM
Replace range names with cell references? KH Excel Worksheet Functions 2 August 2nd 05 01:09 AM


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"