Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing cell references automatically | New Users to Excel | |||
Absolute cell references and subsequent problems. | Excel Discussion (Misc queries) | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions | |||
Sheet Names and Cell References | Excel Worksheet Functions |