HELP: Last row of the column containing blank cells between non-blank cells

S

Space Ape

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.
 
R

Ron Coderre

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?
 
A

Aladin Akyurek

Space said:
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.
Click to expand...

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)
 
Top