last data row

[galina]

How do I reference the last data row in a formula? I need to include a range like $A$14:$A$[last row].

 

[Marcelo Branco]

Hi,

Maybe you can create a dynamic range. See Aladin Akyurek explanation on:
http://www.mrexcel.com/forum/showthread.php?t=18954

HTH

M.

 

[Domenic]

If the data contains numerical values, try...

=$A$14:INDEX($A$14:INDEX($A:$A,ROWS($A:$A)),MATCH(9.99999999999999E+307,$A$14:INDEX($A:$A,ROWS($A:$A))))

If the data contains text values, try...

=$A$14:INDEX($A$14:INDEX($A:$A,ROWS($A:$A)),MATCH(REPT("z",255),$A$14:INDEX($A:$A,ROWS($A:$A))))

 

[T. Valko]

Hi,

Maybe you can create a dynamic range. See Aladin Akyurek explanation on:
http://www.mrexcel.com/forum/showthread.php?t=18954

HTH

M.

Moreover, COUNTA is a volatile function that we would want to avoid if we possibly can.

COUNTA is not volatile.

 

[Marcelo Branco]

COUNTA is not volatile.

Good to know. In fact OFFSET is volatile.

M.

 

[T. Valko]

Good to know. In fact OFFSET is volatile.

M.

Volatile functions:

CELL
INDIRECT
INFO
NOW
OFFSET
RAND
RANDBETWEEN
TODAY

INDEX is not technically volatile.

http://groups.google.com/group/micr...19b25/bd17a12c743f193b?tvc=1#bd17a12c743f193b