As you point and click, Excel helpfully adds the sheetname to each cell thus
adding a lot of characters and you soon reach the limit of 255.

You can type the addresses into the refer to dialog box....comma
separated.......then hit F2, select the cells in the dialog box and use F4 to
turn them to Absolute references.

You can get about 45 cells in a named range.


Gord Dibben MS Excel MVP

On Wed, 02 May 2007 12:33:36 -0500, Dave Peterson
wrote:

Just to make it clear...

I think that the only limitation that Jim Rech, JE McGimpsey and I have seen is
the length of the string that makes up those addresses.

It sure looks like it could be easy to exceed 255 characters by pointing and
clicking.

Bagia wrote:

Hello,

Thanks for the responses. I understand and know how to create Named Ranges.
My question is...is there a limitation to the number of cells you can have,
because I used click and ctrl-click for about 19 non-adjacent cells and was
able to create a Named Range. Then a few minutes later I wanted to create
another named range, but this time it will only allow me to use 11
non-adjacent cells.

My co-worker also run into this problem as well. We are using MS Excel 2003.

I will try Dave's 2nd suggestion. Does anyone know if there's a glitch with
version 2003 when creating Named Range?

Thanks again

"Dave Peterson" wrote:

Maybe you can try this:

Select your range (click and ctrl-click as much as necessary)
Type the name in the name box (to the left of the formula bar)
(remember to hit enter when you're done)

If that doesn't work, one more test.

Select your range again
hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window
type this:

Selection.name = "TheNameYouWantToUseHere"

Then back to excel to test it out.

Bagia wrote:

Hello,

I hope someone can help me solve this problem. What is the limit on how
many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS
Excel 2003 will allow me to have 16 non-adjacent cells and other times it
will let me do just 11.

Thank you.

--

Dave Peterson