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:**

- 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.

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.

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