Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
KH
 
Posts: n/a
Default Replace range names with cell references?

I have a workbook using extensive range names. We are using a wonderful
program to publish this document to a dashboard, but it does not handle range
names well. The file takes a long time to refresh and open when using range
names. We have proven that we can increase the refresh time by more than 10x
if we use cell references versus range names. Does anybody know of an easy
way to change range names back to the cell references . . . ?
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

KH,

Try the macro below on a copy of your workbook.

HTH,
Bernie
MS Excel MVP

Sub ReplaceNameReferences()
Dim myName As Name
Dim mysht As Worksheet
For Each myName In ActiveWorkbook.Names
For Each mysht In ActiveWorkbook.Sheets
mysht.Cells.Replace myName.Name, _
Replace(myName.RefersTo, "=", ""), xlPart
Next mysht
myName.Delete
Next myName
End Sub



"KH" wrote in message
...
I have a workbook using extensive range names. We are using a wonderful
program to publish this document to a dashboard, but it does not handle range
names well. The file takes a long time to refresh and open when using range
names. We have proven that we can increase the refresh time by more than 10x
if we use cell references versus range names. Does anybody know of an easy
way to change range names back to the cell references . . . ?



  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

Jim Rech posted a nice response at:
http://groups.google.com/groups?thre...%40tkmsftngp03

From: Jim Rech )
Subject: Can I "De-Name" Formula Cell References?
Newsgroups: microsoft.public.excel.misc, microsoft.public.excel
Date: 2001-02-16 13:32:51 PST

To do it to a cell or two first turn on Transition Formula Entry under
Tools, Options, Transition. Then go to the cell and press F2 and Enter.
When you turn off TFE the formula references should be de-named.

If you have a lot of cells to de-name select the range and run this macro:

Sub Dename()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Selection.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

--
Jim Rech
Excel MVP

===========

And since you may have a lot of names that aren't doing anything anymore, I'd
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

But I'd back up the earlier version (with the names) before I started.

KH wrote:

I have a workbook using extensive range names. We are using a wonderful
program to publish this document to a dashboard, but it does not handle range
names well. The file takes a long time to refresh and open when using range
names. We have proven that we can increase the refresh time by more than 10x
if we use cell references versus range names. Does anybody know of an easy
way to change range names back to the cell references . . . ?


--

Dave Peterson
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
Changing cell references automatically Bigweed New Users to Excel 3 April 28th 05 12:27 PM
Absolute cell references and subsequent problems. Pank Mehta Excel Discussion (Misc queries) 2 April 19th 05 11:38 AM
Syntax for inferred cell references donesquire Excel Worksheet Functions 4 April 4th 05 09:29 PM
Adding contents of one cell to a range of cells. CLJinVA Excel Worksheet Functions 1 February 10th 05 11:19 PM
Sheet Names and Cell References Reed Excel Worksheet Functions 1 January 19th 05 11:17 PM


All times are GMT +1. The time now is 12:29 AM.

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"