How can I find bottom row of a spreadsheet

R

Robert Gillard

I have a spreadsheet that has rows added to it by different areas of the
office. I need to set up a "front sheet" that will always show the last
(bottom) row of "sheet1".

I think there is a expression that will do this for me, but I do not know
what it is or how to construct it. (I think it comes down the rows until it
hits a blank then goes back up one row)

Can anybody help with this please

Bob
 
J

Jason Morin

It sounds like there won't be any blank rows. In that
cases, you could use:

=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))

to pull in the last value in col. A of Sheet2. Then
repeat the formula to pull in the last value in col. B,
etc.

HTH
Jason
Atlanta, GA
 
G

Gord Dibben

Robert

By worksheet function........

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1
to return the address of last numeric cell in column A

=LOOKUP(9.99999999999999E+307,A:A)
to return the last numeric value in Column A

And just for helluvit....

=LOOKUP(REPT("z",255),A:A)
to return the last non-numeric value in Column A

=ADDRESS(MATCH(REPT("z",255),A:A),1)
to return the address of last non-numeric cell in column A

By VBA macro..........

Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Select
End Sub


Gord Dibben Excel MVP
 
Top