Replace range names with cell references?

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" <KH@discussions.microsoft.com> wrote in message
news:C8F5E8CF-9A01-449D-9C6D-EBE52D00CF75@microsoft.com...
>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 . . . ?

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

From: Jim Rech (jarech@kpmg.com)
Subject: Re: 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

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" <KH@discussions.microsoft.com> wrote in message
news:C8F5E8CF-9A01-449D-9C6D-EBE52D00CF75@microsoft.com...
>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 . . . ?

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

From: Jim Rech (jarech@kpmg.com)
Subject: Re: 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

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 . . . ?

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" <KH@discussions.microsoft.com> wrote in message
news:C8F5E8CF-9A01-449D-9C6D-EBE52D00CF75@microsoft.com...
>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 . . . ?

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

From: Jim Rech (jarech@kpmg.com)
Subject: Re: 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