identifying last entry in a column

Posted by Drew on July 11, 2000 10:38 AM

What formula would you use to identify the last entry in a column where very other row is blank? For example:

A1 12
A2
A3 14
A4
A5 8

In this case, I'd like a formula to display 8 at the top of the workbook. This is for a checkbook where it will always show the current balance at the top. I could put a formula in place of the blank data to make it easier.

I tried =INDEX(A7:A100,COUNTA(A1:A100),1) but returns 0 since there are no entries after A5.

Any ideas? Thanx! Drew

Posted by Ryan on July 11, 0100 11:33 AM

Drew,

Here is a User Defined Function for your needs. If you put this in a module in the workbook you are in or your personal workbook, and then use the formula:
"=LastNum(A)"
This will return the last number in column A, use B for B and so on.
Let me know if this fits your needs!
Ryan

Function LastNum(col)
If ActiveCell.Column = col Then
LastNum = Range(col & ActiveCell.Row - 1).End(xlUp).Value
Else
LastNum = Range(col & "9999").End(xlUp).Value
End Function