Google Sheets: Find last row in range - custom formula

Question

I've been looking for a custom function for Google Sheets which finds the last row which has data.

I wanted to be able to give a range and find the last row with data, as well as being able to specify which column(s) it would give as output.

For example, searching the range A:A for its last row with an entry, and returning the same row from C:D. In this case it would result in [["cool","beans"]] as the output.

+---+--------+------+-------+------+
|   | A      | B    | C     | D    |
+---+--------+------+-------+------+
| 1 | Words  |      | Then  |      |
+---+--------+------+-------+------+
| 2 | and    | asd  | a     |      |
+---+--------+------+-------+------+
| 3 | things |      | thing | Yep  |
+---+--------+------+-------+------+
| 4 |        | here |       | Why? |
+---+--------+------+-------+------+
| 5 | stuff  |      | cool  | beans|
+---+--------+------+-------+------+
| 6 |        |      |       |      |
+---+--------+------+-------+------+

I found a few solutions, but some only allowed checking a single column, didn't handle gaps well or required the use of quotation marks, meaning formula drag and drop functionality was lost. Or using a very long combination of functions, such as this.

I decided to make my own custom formula and you can view my solution below.

Hi! Stack Overflow is a place where you can post your coding doubts or help other with their own doubts, it's not the correct place for presenting a tutorial even it's a good one. — alberto vielma
@alberto Disagree partially. As long it's in a question answer format and the intention is to answer the question raised/add important content, I think it's ok. Related discussion: Can I answer my own questions, even if I knew the answer before asking? — TheMaster

MisterFro9 MisterFro9 · Accepted Answer · 2020-02-13T04:40:58

I created a custom function using google apps script. (In Google Sheets: tools --> script editor)

I apologise to the pros out there if I haven't followed convention in some cases! I self-taught to create this.

It will check a given range and output all values from the final row or, if specified, the final corresponding row from another range/subset of given range.

Example usage: LASTROW(A:A,B:D) will check the range A:A for final non-empty row, and return the corresponding row of B:D.

As long as the number of rows is equal this will work eg. LASTROW(A1:A10,B21:B30)

/**
* Returns the last row in a range, can be single or multi-column.
*
*
* @param    {A1:A100}   range_to_check Range to find final row 
* and returns contents if no range_to_return is specified.
* @param    {[B1:E100]} range_to_return [optional] Range for row to be returned. 
* i.e if row 3 in range_to_check is final row, then row 3 in range_to_return will be returned.
* @customfunction
*/

function LASTROW(range_to_check,range_to_return) {
  // checks range_to_check for final row with non-empty entries
  // range_to_return is optional, will return final row of range_to_check if missing

  if (Array.isArray(range_to_check)) {                                                                                                                  
    // checks if input is a range/cell reference 
    // N.B: Google Apps Script parses a cell range as an array of values

    for (i=0;i<range_to_check.length;i++) {
      // For each row i, check if any columns are non-empty
      var final_column_in_row = undefined;

      for (j=0;j<range_to_check[i].length;j++) {
        if (range_to_check[i][j] !== '') {       
          final_column_in_row = j;
        }
      }
      if (final_column_in_row != undefined) {  // if any columns in row i are non-empty      
        var final_row = i; // set row i as final column
      }
    }
    if (final_row != undefined) {
      // if there exists a non-empty row

      if (range_to_return == undefined) {
        // and no range_to_return is specified

        return [range_to_check[final_row]];
        // return the final row of range_to_check

      } else {
        // if range_to_return is specified

        if (range_to_check.length == range_to_return.length) {
          // and both ranges have the same number of rows

          return [range_to_return[final_row]];
          // return the corresponding row from range_to_return
          // i.e if final row of range_to_check is 5, returns the 5th row of range_to_return

        } else {
          throw 'Ranges have different number of rows, or range_to_check is not an array.'
        }
      }
    } else {
      return null;
    }
  } else {
    throw 'range_to_check is not a range.';
  }

}

Hope someone out there finds some use out of this :)

Google Sheets: Find last row in range - custom formula

1 Answers