Find last/previous non-blank cell in current column

[NickDanger]

I would like to be able to grab a value from a cell in the curren
column, starting at the current row, and looking backwards/upwards t
retrieve the last non-blank entry.

Can someone explain the formula or combination thereof that I could us
to make this happen? I'm not sure if this matters, but this colum
contains date values.

Thanks

 

[Don Guillett]

try this
Sub uprow()
x=ActiveCell.End(xlUp).Row
msgbox x
End Sub

 

[Frank Kabel]

Hi
try something like
=LOOKUP(9.99999999999999E+307,A1:A100)

You have to put this formula outside the range A1:A100 (otherwise you
would get a circular reference error). Format this target cell as date

 

[Bernie Deitrick]

Nick,

If you are in column A, use this formula: change the A1 to B1 if you are in
column B, etc...

=LOOKUP(9.99999999999999E+307,OFFSET(A1,0,0,ROW()-1,1))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I should have also noted that because you are finding a date, you will need
to format the cell with the formula as a date.

Sorry about that,
Bernie

Nick,

If you are in column A, use this formula: change the A1 to B1 if you are in
column B, etc...

=LOOKUP(9.99999999999999E+307,OFFSET(A1,0,0,ROW()-1,1))

HTH,
Bernie
MS Excel MVP