Find last non-blank row with a worksheet function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Perfect,

Thanks Biff.

Bruce

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

First Non-Blank Cell 6
row number of last non-blank cell 14
Finding non contiguous positive values 1
Last non-blank row 8
Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1
COUNT rows on external sheet if between date range 3
Copy, next blank row? 3
Search for specific text within last non blank cell in a range 2

Back
Top