How to Use Excel Formula to Find Last Row Number with Data: 2 Easy Ways

In this article, we will use an Excel formula to find the last row number with data for two cases – either when the last row is blank or when it is non-blank. The output of the last row number with data will not be the same in both cases, so we will use different approaches.

Scenario 1 – Using an Excel Formula to Find Non-Blank Last Row Number with Data

Method 1.1 – Using a Formula with ROW and ROWS Functions

The ROW function in Excel returns the row number from the active worksheet.

The ROWS function in Excel returns the number of rows in a specified reference.

We will find the last row number of the following dataset in cell E5.

STEPS:

Select cell E5.
Insert the following formula in that cell:

=ROW(B5:C15) + ROWS(B5:C15)-1

Press Enter.
The above action returns the row number of the last row from the data range in cell E5. We can see that the number of the last row is 15.

Method 1.2 – Using a Formula combining MIN, ROW, and ROWS Functions

The MIN function in Excel returns the data’s smallest number value from a data range.

STEPS:

Select cell C5.
Insert the following formula in that cell:

=MIN(ROW(B5:C15))+ROWS(B5:C15)-1

Press Enter.
The above command returns the number of the last row in cell E5.

How Does the Formula Work?

ROW(B5:C15))+ROWS(B5:C15)-1: This part returns the array of row numbers from the last row, which is row number 15.
MIN(ROW(B5:C15))+ROWS(B5:C15)-1: Returns the minimum row number in cell E5 which is row number 15.

Method 1.3 – Using a Formula combining ROW, INDEX, and ROWS Functions

In Microsoft Excel, the INDEX function returns the value at a certain position in a range or array.

We will find the number of the last row from the following dataset.

STEPS:

Select cell E5.
Input the following formula in that cell:

=ROW(INDEX(B5:C15,1,1))+ROWS(B5:C15)-1

Press Enter.
We get the last row number of our data range in cell E5 which is 15.

How Does the Formula Work?

INDEX(B5:C15,1,1): This part creates an array of the data range (B5:C15).
ROWS(B5:C15)-1: This part subtracts 1 from the total row numbers.
ROW(INDEX(B5:C15,1,1))+ROWS(B5:C15)-1: Returns the minimum row number in cell E5 which is row number 15.

Scenario 2 – Finding Both Blank and Non-Blank Last Row Number with Data in Excel

Method 2.1. Inserting the MAX Formula to Find the Last Row of Data in Excel

The Excel MAX function provides the greatest value in a specified set of data.

We see that the last row of the following dataset doesn’t contain any value.

Let’s see the steps to do this method.

STEPS:

Select cell E5.
Input the following formula in that cell:

=MAX((B:B<>"")*(ROW(B:B)))

Press Enter.
We get the last row number in cell E5 which is 14. It excludes the last row of our data range which is blank.

Method 2.2 – Combining MATCH and REPT Functions to Find the Last Row of Data in Excel

The MATCH function in Excel searches a range of cells for a specified item and then returns the item’s relative location in the range.

The REPT function in Excel repeats specific text a given number of times. We can use the REPT function to fill a cell with multiple instances of a text string.

STEPS:

Select cell E5.
Insert the following formula in that cell:

=MATCH(REPT("z",50),B:B)

Press Enter.
In cell E5 we get the number of the last row with data in our dataset.

How Does the Formula Work?

REPT(“z”,50): This part repeats the text ‘z’ 50 times.
MATCH(REPT(“z”,50),B:B): In this part, the MATCH function looks in column B for our 50-character text string of ‘z’. The formula returns the location of the last non-blank cell since it cannot find it.

Method 2.3 – Using the Excel LOOKUP Formula to Find the Last Row of Data in Excel

The LOOKUP function belongs to the Excel Lookup and Reference functions. The LOOKUP function returns the comparable value from another one-row or one-column range after performing an approximate match lookup.

STEPS:

Select cell E5.
Enter the following formula in that cell:

=LOOKUP(2,1/(B:B<>""),ROW(B:B))

Press Enter.
We can see the last row number of our data range in cell E5 which is 14.

Method 2.4 – Using SUMPRODUCT Function to Find the Last Row of Data in Excel

The SUMPRODUCT function in Excel returns the sum of matching ranges or arrays’ products.

STEPS:

Select cell E5.
Enter the following formula in that cell:

=SUMPRODUCT(MAX((C5:C15<>"")*ROW(C5:C15)))

Press Enter if you are using ‘Microsoft Office 365’. Otherwise you have to press Ctrl + Shift + Enter to run an array.
We get the last row number with data in cell E5.

How Does the Formula Work?

ROW(C5:C15): This part returns the row number for each cell in range (C5:C15).
MAX((C5:C15<>””): This part returns the highest number from the array of row numbers.
SUMPRODUCT(MAX((C5:C15<>””)*ROW(C5:C15))): The SUMPRODUCT function is used to calculate the above two arrays and return a value in the selected cell.

Method 2.5 – Using VBA code to Find the Last Row with Data in Excel

STEPS:

Right-click on the sheet name of the active sheet.
Click on the option ‘View Code’.

A new blank VBA module will appear.
Enter the following code in the blank module:

Sub Find_Last_Row()
Dim Last_Row As Long  
    On Error Resume Next
    Last_Row = Cells.Find(What:="*", After:=Range("B1"), _
                    LookAt:=xlPart, LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    On Error GoTo 0  
    MsgBox "Last Row: " & Last_Row
End Sub