Mass renaming of named ranges

[Fishboy]

Hi all,

Another day another query it seems! I have an Excel 2010 workbook and am looking to mass rename a whole load of named ranges. Having spent the last 30 or so minutes doing so manually, I then scrolled through the list and realised I wasn't even a quarter of the way through yet.

Fortunately there is a common factor for all the ranges yet to be renamed and that is the fact they all start with L and none of the amended ones do, so what I am hoping is for some simple VBA that will check the current workbook for named ranges beginning with L and add Charts_ as a new prefix to the existing name.

Is anyone able to offer any suggestions please?

Many thanks!

 

[davesweep]

Does this get you started?

Sub a()
For Each nm In ThisWorkbook.Names
If Left(nm.Name, 1) = "L" Then nm.Name = "Chart_" & Mid(nm.Name, 2)
Next
End Sub

 

[Fishboy]

Hi Dave,

Thanks for your reply. I'll give this a quick test on a copy of my workbook and hopefully get back to you shortly with some positive feedback!

 

[Fishboy]

Hi again Dave,

That was great. I did have to make a very minor adjustment (namely changing "Chart_" to "Chart_L") so that it added Chart_ as a prefix to the existing name without replacing the L at the start of the name, but without your initial code I would have been here for hours.

Many thanks mate. A great big LIKE to your post.

 

[davesweep]

Glad to help!