In this article, we will learn how to get last row number of the last non blank cell in a column in Microsoft Excel.
Consider a scenario in which you want to find the last non blank cell in a column.
Using a combination of three functions including ROW, COUNTA, and OFFSSET, you can devise an excel formula for last row which will find out the cell number of the last non blank cell in a column.
ROW: Returns the row number of a reference.
Syntax: =ROW(reference)
Reference: It is a reference to a cell or range of cells.
COUNTA: Counts the number of cells in a range that is not empty.
Syntax: =COUNTA(value1,value2,...)
value1: The first argument representing the values that you want to count.
value2: This is optional. It represents the values that you want to count
There can be maximum 255 arguments. Refer below screenshot:
OFFSET: Returns a reference to a range that is a given number of rows and columns from a given reference.
Syntax: =OFFSET(reference,rows,cols,height,width)
reference: It is a reference to a cell or range of cells from which the OFFSET will be applied.
rows: The number of rows up or down that you want to apply as the offset to the range.
cols: The number of columns left or right that you want to apply as the offset to the range.
height: This is optional. It is the number of rows that you want the returned reference to be.
width: This is optional. It is the number of columns that you want the returned reference to be.
Let us take an example:
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
M Blasco's worked for me
Sid Le SLoth did not work at all, the formula had an error in it.
This formula, as already mentioned, is flawed. David / M Blasco's answer didn't work for me (Excel 2016). I found this formula at ExtendOffice.com (How to return the row number of first or last non blank cell in worksheet?):
=SUMPRODUCT(MAX((A:A"")*ROW(A:A)))
Don't ask me how it works!
David's answer almost worked for me, but I had to modify it a little (note: this formula won't work in the same column you are inspecting):
=MAX(IF(ISBLANK(A:A),0,ROW(A:A)))
Absolutely correct to Igor comments. Why do need all this if we can do it with COUNTA(A:A) ?
How do I know the last row if I have empty cells in column A?
Please help
MAX(ARRAYFORMULA(IFERROR(IF(ISBLANK(A:A),0,ROW(A:A)))))
There may be intervening blank cells. Counta will not be accurate.
Overcomplicated.
This would not work if there are a few empty cells in column A. Yet if this is not important, why not just count the number of non-blank cells. =COUNTA(A:A) will give the same result.