Named Range Limitations

[Guest]

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.

 

[Jim Rech]

The limit in the Define Name dialog's Refers To box is 255 characters. So
the number of areas a name created using it can have depends on their
addresses and the length of the sheet's name.

--
Jim
| 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]

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.

 

[JE McGimpsey]

As a workaround, note that you can use the union operator (,) to break
your ranges into smaller units:


Name in workbook: myrange1
Refers to: =Sheet1!$A$1:$F$1

Name in workbook: myrange2
Refers to: =Sheet1!$J$10:$K$15

Name in workbook: myrange3
Refers to: =(myrange1,myrange2)

 

[Guest]

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

 

[JE McGimpsey]

You haven't addressed the suggestion in a previous response as to
whether you're exceeding the 255 character limit for a defined name.

Since for each non-adjacent cell, the defined name will include the
sheet name, a ! character and the address, a small change in sheet name
length can significantly change the maximum number of cells in a named
range.

 

[Guest]

I definitely did not exceed the 255 character limit. Sorry for not addressing
the suggestion.

 

[Dave Peterson]

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.

 

[Gord Dibben]

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