Named Range - Limit on Cells?

[mattygriff]

I'm trying to create a named range but am coming up against an issue.

The cells are not contiguous, rather are split across different rows - for example,

='ECC Form 9'!$D$9:$K$9,'ECC Form 9'!$D$11:$K$11,'ECC Form 9'!$D$13:$K$13,'ECC Form 9'!$D$15:$K$15,'ECC Form 9'!$D$17:$K$17,'ECC Form 9'!$D$19:$K$19

My problem is that I need to extend this range to take in more rows which works OK for rows 21, 23, 25 and 27. However, when I try and include row 29, the "Define Name - Refers to:" box clears itself and I just get a single equals sign.

I'm guessing there's a limit on non-continuous groups of cells that can be included in a Named Range - can anyone confirm and suggest a solution?

Thanks.

 

[SkipVought]

Hi,

Why every other row?

Excel is designed to work with best and accepted table formats. Every other row is an odd design.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Yes there is a limit to the number of indicidual discreet ranges you can add into a named range

Not sure what you are trying to do here but given the name of the worksheet is "form" I would guess that these are data entry cells that you will need to clear out / populate?

If that is the case, what I tend to use is a bunch of seperate named ranges but with a specific naming convention that I can then test in code such as starting each name with "de" for data entry or such like...if you let us know what you are trying to achieve, we may be able to come up with a solution...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mattygriff]

I agree that it's not a perfect design to use alternate rowns but it's how the form is laid out - not under my control, unfortunately.

From what's been said, I'm going to have to change my approach slightly - split things down a bit rather than use a single range.

Thanks both.