How to replace *some, but not all* Named Ranges with their cell references?

d0rian

Active Member
Joined
May 30, 2015
Messages
293
My file has 500+ named ranges that I use in 2 places:
1) formulas throughout the 15 sheets of the workbook
2) "Module1", a single VBA module where all my macro codes live

I want to replace some (but not all) Named Ranges with their actual cell references. I used the Formulas...Defined Names...Use in Formula...Paste Names...Paste List function to create a 2-column list on a new sheet (sheet name: "names") of all the Named Ranges and their references, and I've pasted the ones I want replaced into columns C and D.

TLDR: See image below. I have pasted the named ranges and their references that I want replaced into columns C and D (yellow cells). What code can I use to replace C1 with D1, C2 with D2, etc etc. The replacement needs to happen in two places:
1) Throughout the workbook
2) In "Module1" where all my VBA code lives.


named_ranges.JPG
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
I hope this wll help:

Delete a Named Range
1) Open Microsoft Excel, then click "File" and open the document containing the named range you want to delete.
2) Click the "Formulas" tab and click "Name Manager" in the Defined Names group. A window opens that contains a list of all the named ranges in the document.
3) Click the name you want to delete. If you want to delete multiple names in a contiguous group, press the "Shift" key while clicking each name. For names in a non-contiguous group, press "Ctrl" and click each name you want to delete.
4) Click "Delete," then confirm the deletion by clicking "OK."

Change a Named Range
1) Launch Microsoft Excel and open the file containing the name you want to replace.
2) Click the "Formulas" tab. Click "Name in Manager" under the Defined Names heading.
3) Click the name you want to replace, then click "Edit" in the Name Manager box.
4) Enter a new name for the range in the Name box. Change the reference for the name in the Refers To box. Click "OK."
5) Change the formula, constant or cell the name represents in the Refers To field in the Name Manager box. Click "Commit" to accept the changes.

also it depends where do u use that named range, even if you have a named range, you still can use basic reference to a range.

not sure what you want to do, but if you would present your whole plan - maybe there would be easier way?
 

d0rian

Active Member
Joined
May 30, 2015
Messages
293

Hm, not sure you really read the question in the OP; I described pretty clearly what I'm trying to do, but your post is mostly just basics about how Named Ranges work..my question is more specific about how to do a specific replacement task.
Anyone else?
 

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
Yes i read it. what I dont get then is why basic removal wont work?
especoialy since its just 3 of them?
In addition if you type the desired range as cell references manually it wont change to named range.
 

d0rian

Active Member
Joined
May 30, 2015
Messages
293
Yes i read it. what I dont get then is why basic removal wont work?
especoialy since its just 3 of them?
In addition if you type the desired range as cell references manually it wont change to named range.

It's not just 3 of them. There's only 3 in the picture because I wanted to give a representative picture of the task...but there's 300-400 Named Ranges I want replaced with their cell references. If it were just a handful, you're right, I would have done it manually.
 

Forum statistics

Threads
1,147,564
Messages
5,741,856
Members
423,691
Latest member
Fahad987

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