How can I get the Last row of the column containing blank cells between
non-blank cells.

Example:

the data in a column is

1
(blank cell)
2
3
4
5

How can I get last row of this column?

Thanks in advance for your replies.

For data from A1:A10
-------------------------
If you want the row number of the last data item:
=SUMPRODUCT(MAX((A1:A10<"")*ROW(A1:A10)))

If you want the value of the last data item (and your data starts at A1:
=OFFSET(A4,SUMPRODUCT(MAX((A4:A10<"")*ROW(A4:A10) ))-1,0)

For data from A4:A10
-------------------------
If you want the value of the last data item (and your data starts at a row
other than A1:
=OFFSET(A4,SUMPRODUCT(MAX((A4:A10<"")*ROW(A4:A10) ))-ROW(A4),0)

Does that give you something to work with?

--
Regards,
Ron


"Space Ape" wrote:

How can I get the Last row of the column containing blank cells between
non-blank cells.

Example:

the data in a column is

1
(blank cell)
2
3
4
5

How can I get last row of this column?

Thanks in advance for your replies.

Space Ape wrote:
How can I get the Last row of the column containing blank cells between
non-blank cells.

Example:

the data in a column is

1
(blank cell)
2
3
4
5

How can I get last row of this column?

Thanks in advance for your replies.


The native row number wtr the last numeric value:

=MATCH(9.99999999999999E+307,A:A)

if the column of interest is A.

To fetch the last numeric value itself:

=LOOKUP(9.99999999999999E+307,A:A)