I'm trying to set up a 6 column dynamic named range where all of the
columns could possibly have text or number (ie Number Format = General)
as well as any number of blanks.

To locate the bottom-most row with data I have adapted a formula from
Bob Phillip's xldynamic website that uses MATCH and MAX with
"ZZZZZZZZZZZZZZZ" and 9.99999999999999E+307.
To get the formula to work on my old iMac I've had to include ISERROR.
The final formula looks like...

=MAX(MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.999 99999999999E+307},$B:$B)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$B :$B))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$C:$C)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$C :$C))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$D:$D)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$D :$D))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$E:$E)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$E :$E))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$F:$F)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$F :$F))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$A:$A)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$A :$A))))

(the column addresses are out of order, but that doesn't matter, it
still works)

This has too many characters (785) to go into the Refers to: box on the
Define Names dialog.
I think the limit is 255 characters.

I tried reducing the characters using "ZZZZZZ" and 9.9E+307 but it's
still too big with 520 characters.

Also, this formula is only the Height argument of the OFFSET formula in
the Refers to: box.

To overcome this problem I have entered the above formula into G1 and
the formula ="$A$2:$F$" & G1
into G2.

I will then hide column G.

Then the formula in the Refers to: box is...

=INDIRECT(Sheet1!$G$2)

This works, but I can't help feeling there is any easier way.

Any ideas?

Ken Johnson