Vfrdtyky

//Sample sheet here

Hi,

I am using formulas to calculate an array N:R. Once calculated, I want to determine the last row of the array with a non-empty cell (the empty cells are not blank).

What I can do so far:

Return the last non-empty cell of a column

=INDEX(FILTER(O:O,O:O<>""), ROWS(FILTER(O:O,O:O<>"")))

or the row of the filter selection (in my case 25 in the filter selection vs 38 in the sheet)

=ROWS(FILTER(O:O,O:O<>""))

What I haven't figured out is how to:

1. Do this search for the whole array and not just one row at a time


2. Return the row of the last non-empty cell in the array

Cheers

This custom function will do it. Sometimes scripts are way easier than some of the bizarre formulas that arise (IMHO). It just loops through the data row by row and notes the row number if it finds data ie cell.value() != ""

function findHighestNonEmptyRow(){

  var sheet = SpreadsheetApp.getActive();

  var range = sheet.getRange("N:D");

  var valuesRC = range.getValues();

  var numRows = range.getNumRows();

  var numCols = range.getNumColumns();



  var highestNonEmptyRow = 0;



  for (var row = 0; row < numRows; row++) {

    for (var col = 0; col < numCols; col++) {

      if (valuesRC[row][col] != ""){

        highestNonEmptyRow = row+1;  // +1 to offset loop variable

      }

    }

  }

  Logger.log(highestNonEmptyRow);

  return highestNonEmptyRow;

}

Log show correct value of 38. You can delete the Logger.log(highestNonEmptyRow); line when you have tested.

I put the formula in W44 in your test sheet....

For a formulaic approach, you can try

=max(Arrayformula(filter(row(N2:N), MMULT(N(N2:R<>""), transpose(column(N2:R2)^0))>0)))