replace all defined range names in the sheet that start with "Street" to "Road"

Hi everyone!

I want to replace all defined range names in the sheet that start with "Street" to "Road"

For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"

I want to change them all in vba to Road-01", "Road-02" etc.

Thanks.

Thanks Mallycat

A slight problem.

The line:
Application.Goto Reference:=myOldName
is giving me the following error "Reference is not valid"

Also The Street names do not have to be sequential or even ending with two digits. Some are "Street_a97" for example. What I want is to replace the "Street_" part by the "Road_" part only, leaving the rest of the string the same. (i.e. Road_a97")

Hope you can help

Hello matrex,

Here is macro to change the range names.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Mm, that looks like a good solution

Hi Leith

Looked like it was going to work, but no changes were made to the names!

Hello martex,

interesting, it ran fine on my test sheet. I am using Excel 2003. Are you using a different version of Excel?

Sincerely,.
Leith Ross

Excel 2002. Also tried in on Excel 2007

See attached sample file.

Hello Matrex,

Got it fixed. Here is the revised macro. It has been added to the workbook also.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

mudraker and Leith

Excellent! Both codes worked like a charm.

Thanks so much and have a great day/evening

Hello Matrex & Mudraker,

My typing skills aren't the best. Sometimes my transcriptions from my workbook to the forum are incomplete. I try to be vigilant in catching those errors, but some slip by. I appreciated the help from both of you and your patience.

Sincerely,
Leith Ross

Hi... say i have a set of name ranges from rows 1 to 10, and columns A to Z.

I replicated this to rows 20 to 50 and columns A to Z

For this replication, i would like to change the range name by adding _case2... but only the name ranges that falls within rows 20 to 50 and columns A to Z.... how do I embed this in the macro??

Please help

Kind regards
Chris

Okay...

Say from A1:J1 and A10:J10 i have a few name ranges called AAA, BBB, CCC, obviously theres 9 other name range in that block, I duplicated this to apply to A11:J11 and A20:J20. So its still carrying the same name range

I would like to change this name range which i replicated to AAA_case2, BBB_case2, CCC_case2

Rather than using Ctrl H to find and replace those selected rows.... i would like a macro to do this for me so it saves me time
Is this workable?

This worked for me...thank you Leith!