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.
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.
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.
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:
- In column A & B, we have some codes
- We want to find out the last non blank cells. The formula in cell D2 would be =ROW(OFFSET(A1,COUNTA(A:A)-1,0))
- Cell A1 is taken as reference in Offset function
- Count A will be the (rows) second argument of Offset function & will return the total number of rows contained in column A.
- 0 is taken as cols the third argument. Formula will check for column A.
- The value returned by Offset & Count A will be used by Row to calculate the last non blank cell.