Formula for the last used cell in a column

Is there a formula to find the last used cell in a column?

By Neale Blackwood

You can use an array formula to find the last used cell reference. The following formula will find the last used cell in column A, except if the last used cell is A1048576.

articleid_53_formulaforlastusedcellinacolumn_1_400_01 (1)

This is an array formula, which means after typing it you must hold down the Ctrl and the Shift key and then press Enter. Excel will put {} brackets around the formula. You can’t use this formula in column A as it will give you a circular reference error. A variation on this formula is another array formula:

=INDIRECT(ADDRESS(MAX(ROW(1:65535)*(A1:A65535<>””)),

COLUMN(A:A)))

articleid_53_formulaforlastusedcellinacolumn_3_400 (1)

Remember to hold down Ctrl and Shift and then press Enter after typing. This will display the value of the last used cell in column A, except if it is the last cell in column A.

First published: CPA AUSTRALIA Magazine INTHEBLACK April 2005 – Page 61 Excel Yourself