Is there a way to batch update named ranges?

[qaliq]

Afternoon everyone,

I have a spreadsheet where I created about 100 named ranges, they all start in column L and go to column AE. However, it turns out I didn't look far enough into the future when I was planning my spreadsheet and I now need to extend all of the ranges so that they go to column AM.

Is there a quick way to do this (bearing in mind I have 100 odd names)? or am I going to have to go into the name manager, work my way down the list and change AE to AM for every one?

Anyones help is much appreciated.

Thanks,

Alice

[Andrew-R]

A short macro like this should do the job for you:

Please Login or Register  to view this content.

[Bob Phillips]

Cut some code

Please Login or Register  to view this content.

[qaliq]

Thanks Andrew-R however when I try that I get a Runtime 1004 error saying that the name is not valid (and then the usual reasons, spaces, already exists, doesn't begin with a letter...)

It's almost like the code is renaming the actual name rather than the range - although I would have thought that's what the .refersto bit was supposed to do.

....any ideas?

Thanks,

---------- Post added at 01:32 PM ---------- Previous post was at 01:29 PM ----------

Bob - if I was going to use your method then I guess I would have to list out all my range names in, this bit

Please Login or Register  to view this content.

Also where does it specifiy where I would want to extend the range to?

Forgive me if these are stupid questions - VBA is all very new to me!

Thanks,

[Bob Phillips]

]Bob - if I was going to use your method then I guess I would have to list out all my range names in, this bit

Please Login or Register  to view this content.

That is so.

Also where does it specifiy where I would want to extend the range to?

Where it says

Please Login or Register  to view this content.

the original range is being resized by 1 column. You would need to change that to + 8 to increase from AE to AM.

[qaliq]

Ok great, quick question then...is there a way that I could get excel to list out all my range names (as a continuous list rather than cell by cell like it would do if I did paste names)?

Thanks,

[Bob Phillips]

Please Login or Register  to view this content.

---------- Post added at 02:47 PM ---------- Previous post was at 02:45 PM ----------

If all names are to be updated, you could use

Please Login or Register  to view this content.