I am using a workbook that contains hundreds of range names. I need to extend
all of the ranges so the ranges run to column BB instead of column AP as
they do now.

For example a named range is currently $M$255:$AP$255 and needs changing to
$M$255:$BB$255

Is their any way I can do the changes in one go instead of going through
Insert €“ Name €“ Define, and changing them all manually?

you could put an offset formula as the range for the names range

=OFFSET(name of tab here!$m$255,0,0,COUNTA(name of tab here!$M:$M),COUNTA
(name of tab here($1:$1))



chillibean wrote:
I am using a workbook that contains hundreds of range names. I need to extend
all of the ranges so the ranges run to column BB instead of column AP as
they do now.

For example a named range is currently $M$255:$AP$255 and needs changing to
$M$255:$BB$255

Is their any way I can do the changes in one go instead of going through
Insert €“ Name €“ Define, and changing them all manually?

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

One way

Sub changename()
old a1:b21
Range("a1:x21").Name = "myrng"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"chillibean" wrote in message
...
I am using a workbook that contains hundreds of range names. I need to
extend
all of the ranges so the ranges run to column BB instead of column AP as
they do now.

For example a named range is currently $M$255:$AP$255 and needs changing
to
$M$255:$BB$255

Is their any way I can do the changes in one go instead of going through
Insert €“ Name €“ Define, and changing them all manually?

How about:

Sub namextender()
For Each n In ActiveWorkbook.Names
n.RefersTo = Replace(n.RefersTo, "$AP$", "$BB$")
Next
End Sub
--
Gary''s Student - gsnu200804


"chillibean" wrote:

I am using a workbook that contains hundreds of range names. I need to extend
all of the ranges so the ranges run to column BB instead of column AP as
they do now.

For example a named range is currently $M$255:$AP$255 and needs changing to
$M$255:$BB$255

Is their any way I can do the changes in one go instead of going through
Insert €“ Name €“ Define, and changing them all manually?