< Go Back

Last row number in range

Last row number in a range

As the name goes, to find the last row number in a range of cells we use the Excel built-in functions, the ROW function, and the MIN function.

Generic formula

=MIN(Row(range))+ROWS(range)-1

How does the formula work?

  1. As given above the generic formula contains a ROW function enclosed within a MIN function where the ROW function returns the row number for the given reference cell.
  2. The MIN function here is used to get the first-row number in an array of cells. The idea behind this is to extract the first one value using MIN function which will be the lowest value in the array and once the first row is known then we subtract this from the total rows in the array which is also calculated using the ROW function and finally abstract a ‘1’ from the result to return the last row number in the given range of cells.

Other versions of the Generic formula

Using INDEX version

We can also use the Excel INDEX function rather than using a MIN function and also this formula is faster for a large range of cells because the INDEX function is appended only a single cell to ROW.

Genetic formula

=ROW (INDEX(data,1,1) )+ROWS (data-value) -1

  1. Using a simpler version

            The last row number in a range of cells can also be calculated by using only the ROW  

             Functions as shown below.

       =ROW (data-value) + ROWS (data-value)

             This is a more simplified version, where the generic formula results in the Excel work

             Worksheet displays only the first data in the array result.

Example

  1. Let us consider some random numeric values in the item1 and item2 column as shown. Now let us display the last row using the MIN and ROW functions. The result is as obtained below.

Figure 1. Result obtained using MIN and ROW functions

Let us now use the second method by using INDEX function. The result of using this function is as shown below.

Figure 2. Result obtained using INDEX and ROW functions

Now let’s use only the ROW function (simplified version). The result is as obtained in the figure given below.

Figure 3. Result obtained using only ROW functions

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar