Named Range to Cell References

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to convert around 1000 named ranges to cell references.

I found this method on Reddit below which works until after changing the formulas from = to start with |
The problem is I can't find and replace the | back to = as it's not a formula anymore.

Don't know if there is a way to do this as I believe the logic to replace the = sign is correct to avoid recalculation which will take a long time for each change.


Thanks,

https://www.reddit.com/r/excel/comments/5jfri8
Method:

List out all names and associated ranges with the following macro, it creates a list of names and range from your active cell (alt+F11 - copy to ThisWookbook and run)

Sub listnamesranges()
Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
For r = 1 To nms.Count
On error resume next
wks.Cells(r, 1).Value = nms(r).Name
'wks.Cells(r, 2).Value = nms(r).RefersToRange.Address
wks.Cells(r, 2).Value = "''" & nms(r).RefersToRange.Parent.Name & "'!" & nms(r).RefersToRange.Address
Next
End Sub

Then disable all formulas thus: ctrl+H = to |

Select the first name in the new table of ranges and run this macro

Sub changereferences()
Do Until ActiveCell.Value = ""
rWhat = ActiveCell.Value
rTo = ActiveCell.Offset(0, 1).Value
Cells.Replace What:=rWhat, Replacement:=rTo, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Then enable all formulas thus: ctrl+H | to = (my issue!)

I transformed 50,000 formulas in a split second without issue..

Re-enabling the formulas may take a while depending on complexity.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
No problem seems to be working now!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top