Named Ranges

[Brian]

Is there a limit on the number of non-adjacent ranges you
can give a Named range to?

If I wanted to select cells A1:d1;a5:d5;a17:d17 and
continue this for the next 10 selections, will I run out
of how many selections I can make? Is there a limit to
the amount of selections for named ranges?

Thanks

 

[RagDyer]

Yes, there is a limit of approximately 255 characters.
May sound like a lot, but XL adds the sheet name to each individual cell
address in the range.
So, a WS named "Num1" will be able to accommodate a larger named range then
a WS named "EasternSalesProjections".

If you do run out of room, there are workarounds, one of which can be found
in this old post of Dave Peterson's:

http://tinyurl.com/ypk2j

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Is there a limit on the number of non-adjacent ranges you
can give a Named range to?

If I wanted to select cells A1:d1;a5:d5;a17:d17 and
continue this for the next 10 selections, will I run out
of how many selections I can make? Is there a limit to
the amount of selections for named ranges?

Thanks

 

[Gord Dibben]

Brian

You will run out when you reach 255 characters in the range selection.

From Debra Dalgleish.....

The limit is 255 characters in the Name definition. For example, I can
define a range of 46 non-contiguous cells, with the following string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4,$B$6,$D$6,$F$6,$H$6,
$J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$12,
$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$B$16,$D$16,$F$16,$H$16,
$J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3

Gord Dibben Excel MVP