Two notes...

This will fail when there is either no text or no numeric data. BTW, the
OP is asking for the row number, not the address of the last
numeric/text value.

You state:
"Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for
text, but I have usually found the above to meet my needs."

Maybe so. However, did you consider endless variations the people may
come up with, causing a jungle of constants, hard to explain to new comers?


Roger Govier wrote:
Hi

One way
=CELL("Address",INDEX(A:A,MAX(MATCH(99999999,A:A), MATCH("ZZZZ",A:A))))

This will look for the last numeric or last text value in column A and
return the cell reference where found.
Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for
text, but I have usually found the above to meet my needs.

Regards

Roger Govier


wrote:

Hi TemplateBuilder,

Thank you for your solution but that is not exactly what I am after ...

Imagine you have only 5 values filled in Column A ...bu the 5th is at
row 125 then I would like it to return 125 and not 5 (or 6).

Brgds Sige


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.