Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

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
A3 14
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

Check out our Excel VBA Resources

Re: identifying last entry in a column

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

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:
This will return the last number in column A, use B for B and so on.
Let me know if this fits your needs!

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

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.