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

[d0rian]

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.

 

[Radoslaw Poprawski]

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]

...

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]

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]

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.

 

[Radoslaw Poprawski]

I think the only way is VBA here then, tried to help, but failed - sorry