Find last non-blank row with a worksheet function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bruce - 25 Nov 2005 04:04 GMT

Find the last row with a value.

Say I have a worksheet with a data entery range of A1 to A100. The user is
required to enter data from row 1 downwards and cannot leave blanks between
rows.

I want to return the value in the last comleted row by function. Any ideas?
For example that last row with a value may be in cell A20 of the A1:A100
range.

Rgds,

Bruce

Reply to this Message

Biff - 25 Nov 2005 04:25 GMT

Hi!

Try one of these:

If the data is all numeric:

=LOOKUP(9.99999999999999E+307,A1:A100)

If the data is all text:

=LOOKUP(REPT("Z",255),A1:A100)

If the data is mixed:

=LOOKUP(2,1/(A1:A100<>""),A1:A100)

Biff

> Find the last row with a value.
>
[quoted text clipped - 11 lines]
>
> Bruce

Reply to this Message

Bruce - 25 Nov 2005 05:10 GMT

Perfect,

Thanks Biff.

Bruce

> Hi!
>
[quoted text clipped - 29 lines]
> >
> > Bruce

Reply to this Message