Changing Named Ranges

Hi all,

I have taken over and enhanced an Excel application. It was quite
untidy and I have been trying to clean it up to make it easier to read
for someone else doing future development.

One of the things I would like to do is clean up the convention for
named ranges. The workbook is full of names (approx. 100), most of
which are not easily identified where they come from, although I know
I can use name utilities to identify them (and many thanks to Rob
Bovey, Jan Karel Pieterse, and Charles Williams for their excellent
utilities). But I would like to make the names more easily
recognisable.

To give an example, I would like to use a convention "input_Name" for
named ranges on the input pages, or "summary_Name" for named ranges on
the summary page. (Incidentally, I'm just guessing this is best
practice?)

However, there seems to be no easy way to go through and bulk change
the names, AND make sure the references in the spreadsheet are changed
(and of course there are references in VBA). Instead of just being
able to alter the name, it seems I have to:

• add a new name
• go through the workbook and do a find/replace (checking each one to
make sure its the correct name to change - eg. the name input_location
vs. a title "Location")
• delete the old name
• go through the VBA and do a find/replace (once again checking each
occurrence)

Can anyone advise if there is such a way to more easily enable these
changes? I would have thought that it should be a simple matter to
change the names (at least in the spreadsheets), but I can't find any
such method.

Many thanks for any advice,
Chris Adams

Hi Chris,

> Instead of just being
> able to alter the name, it seems I have to:
>
> • add a new name
> • go through the workbook and do a find/replace (checking each one to
> make sure its the correct name to change - eg. the name input_location
> vs. a title "Location")
> • delete the old name
> • go through the VBA and do a find/replace (once again checking each
> occurrence)

I'm afraid that is the only way right now. Charles Williams has been
working on a renaming routine for our Name Manager, but as you can
imagine this is rather complex material, especially when doing it in
slow business hours <g>.

I would use the Name Manager in combination with my Flexfind to get this
job done.

You may find the "List" and Pick up" feature of the NM very useful to
quickly create the new names:

1. Click the list button
2. Close the name manager and go to the new list
3. Edit each name's name to create a new name with the same RefersTo
string (better yet, first copy the entire table downwards and edit the
copied rows so you have overview of the old and new situation)
4. Start Name Manager and hit the Pick-up button and follow instructions
to add the new names.

Now you have the names named as you need them and have to start finding
and replacing them. I'd create a two-column table first, listing old and
new names next to each other to keep track.
Then I'd use Flexfind to do the S&R, because it enables:

1. S&R in all (well, as far as I know) objects
2. Single entry confirmation within each formula.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Jan,

Many thanks for the reply. Yes, I can believe the complexity of
Charles' task .

I have just tried Flexfind - a powerful little utility. Really useful
indeed, and complements the Name Manager exceptionally well. I'm
following your advice below and its working quite well considering the
task.

Many thanks again for your advice and especially the utilities - very
much appreciated.

Kind regards,
Chris Adams



Jan Karel Pieterse <[email protected]> wrote in message news:<[email protected]>...
>
> I'm afraid that is the only way right now. Charles Williams has been
> working on a renaming routine for our Name Manager, but as you can
> imagine this is rather complex material, especially when doing it in
> slow business hours <g>.
>
> I would use the Name Manager in combination with my Flexfind to get this
> job done.
>
> You may find the "List" and Pick up" feature of the NM very useful to
> quickly create the new names:
>
> 1. Click the list button
> 2. Close the name manager and go to the new list
> 3. Edit each name's name to create a new name with the same RefersTo
> string (better yet, first copy the entire table downwards and edit the
> copied rows so you have overview of the old and new situation)
> 4. Start Name Manager and hit the Pick-up button and follow instructions
> to add the new names.
>
> Now you have the names named as you need them and have to start finding
> and replacing them. I'd create a two-column table first, listing old and
> new names next to each other to keep track.
> Then I'd use Flexfind to do the S&R, because it enables:
>
> 1. S&R in all (well, as far as I know) objects
> 2. Single entry confirmation within each formula.
>
> Regards,
>
> Jan Karel Pieterse
> Excel MVP
> http://www.jkp-ads.com

Hi Chris,

> I have just tried Flexfind - a powerful little utility. Really useful
> indeed, and complements the Name Manager exceptionally well. I'm
> following your advice below and its working quite well considering the
> task.

Thanks for the compliments.

> Many thanks again for your advice and especially the utilities - very
> much appreciated.

No thanks!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com