Find LastRow of column C (when Col A and B have a different row size)?

Question

How to find the last used cell of column C ?

Example: "Sheet1" : "Col A" and "Col B" have 1200 rows. And "Col C" has only 1 row.

## ColA  ColB   ColC
##    1     1      1
##    2     2   empty
## ..    ..     ..
## 1200  1200   empty

Here are my unsuccessful tests :

Function find_last_row_other_column() {
var ws_sheet =
var ws = SpreadsheetApp.openById("Dy...spreadsheet_id...4I")
var ws_sheet = ws1.getSheetByName("Sheet1");

var lastRow = ws_sheet.getRange("C").getLastRow();
var lastRow = ws_sheet.getRange("C:C").getLastRow();
var lastRow = ws_sheet.getRange(1,3,ws_sheet.getLastRow());  1200 rows for colA! instead of row = 1 for col C.
}

Note: I can't use C1 because next time I use the function it will be C1200 or something else.

var lastRow = ws_sheet.getRange("C1").getLastRow();

I ask this because my next goal is to copy/paste the result of C1 into C2:C1200. Here is my test :

var lastRow = ws_sheet.getLastRow();
var target_range = ws_sheet.getRange(1,3,lastRow,1); //C1 until last row
var Formula_values = source_range.getValues();
target_range.setValues(Formula_values);

Thanks in advance ;)

ps: I have spend 2 hours on it. I have tried similar problems & their solutions already given on this website, but I can't happen to make them working. I am lost ! : More efficient way too look up the last row in a specific column? and Get last row of specific column function - best solution

Answer 1

As I mentioned in the comments above, this is the subject of the highest score post on StackOverFlow...

The original post returns the value of the last cell in a column but a (very) little modification makes it return the row index.

Original post :

Script:

function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return values[lastRow - 1];
}

modified to return index of the last used cell in a column :

function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return lastRow;
}

Answer 2

Here is the function to do it:

function lastRowInColumnLetter(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow() - 1; // values[] array index
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + (lastRow + 1)).getValues();
  while (lastRow > -1 && values[lastRow] == "") {
      lastRow--;
  }
  if (lastRow == -1) {
    return "Empty Column";
  } else {
    return lastRow + 1;
  }
}

and you invoke it as =lastRowInColumnLetter("C").

And here are 3 more useful functions in this context:

function lastValueInColumnLetter(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow() - 1; // values[] array index
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + (lastRow + 1)).getValues();
  while (lastRow > -1 && values[lastRow] == "") {
      lastRow--;
  }
  if (lastRow == -1) {
    return "Empty Column";
  } else {
    return values[lastRow];
  }
}

function lastValueInColumnNumber(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow() - 1; // values[] array index
  var values = SpreadsheetApp.getActiveSheet().getRange(1,column,lastRow + 1).getValues();
  while (lastRow > -1 && values[lastRow] == "") {
      lastRow--;
  }
  if (lastRow == -1) {
    return "Empty Column";
  } else {
    return values[lastRow];
  }
}

function lastRowInColumnNumber(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow() - 1; // values[] array index
  var values = SpreadsheetApp.getActiveSheet().getRange(1,column,lastRow + 1).getValues();
  while (lastRow > -1 && values[lastRow] == "") {
      lastRow--;
  }
  if (lastRow == -1) {
    return "Empty Column";
  } else {
    return lastRow + 1;
  }
}

These functions properly address empty columns, and also start counting backwards from the last row with content on the active sheet getLastRow(), and not from the last row on the sheet (with or without content) getMaxRows() as in the accepted answer.

Answer 3

If you don't have empty cells between your data, you can use this:

function last_Column_Row(){ 
  var ss = SpreadsheetApp.getActive(); 
  var sheet = ss.getActiveSheet();
  var Direction = SpreadsheetApp.Direction;
  var xcol = 2;//e.g. for column 2 ("B"), to obtain its last row
  var yrow = 8;//e.g. for row 8, to obtain its last column

  var lastRow =sheet.getRange(1,xcol).getNextDataCell(Direction.DOWN).getRow();//last row of column 'xcol'
  var lastCol =sheet.getRange(yrow,1).getNextDataCell(Direction.NEXT).getColumn();//last column of row 'yrow'
};

It gets the number of next empty cell-1 of a specific row or column (similar to Ctrl + 'arrow' in a sheet)

But If you have empty cells between your data, you can use this:

function last_Row_Column2()
{ 
  var ss = SpreadsheetApp.getActive(); 
  var sheet = ss.getActiveSheet();
  var Direction = SpreadsheetApp.Direction;
  var maxR =sheet.getMaxRows();
  var maxC = sheet.getMaxColumns();

  var yrow = 8;//e.g. for row 8, to obtain its last column
  var xcol = 2;//e.g. for column 2 ('B'), to obtain its last row

  var valMaxR = sheet.getRange(maxR,xcol).getValue();//for the case that the last row has the last value
  var valMaxC = sheet.getRange(yrow,maxC).getValue();//for the case that the last column has the last value

  if(valMaxR !=''){var lastRow = maxR;}//if the last row in studied column is the last row of sheet
  else{var lastRow =sheet.getRange(maxR,xcol).getNextDataCell(Direction.UP).getRow();}

  if(valMaxC !=''){var lastCol = maxC;}//if the last column in studied row is the last column of sheet(e.g.'Z')
  else{var lastCol =sheet.getRange(yrow,maxC).getNextDataCell(Direction.PREVIOUS).getColumn();}
};

Answer 4

[UPADTE} Please disregard this answer. User Serge's code instead. I was having a brain fart. His answer is magnitudes better in every way. That will teach me not to answer SO questions after you come back from a cocktail night... [/UPDATE]

The following function will log the last non-blank row number of column C. Note: if, for example, column C has a value in row 1 and row 200, with rows 2-199 blank, the function will return 200 as last non-blank row - it does not account for blank rows above last non-blank row.

function getLastNonBlankColCrow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastNonBlankColCrow = 0;
  for (var i=1, lenRows=sheet.getRange("C:C").getNumRows(); i<=lenRows; i++) {
    if ( !sheet.getRange(i, 3).isBlank() ) { // 3 is 1-based index of column C
      lastNonBlankColCrow = i;
    }
  }
  Logger.log(lastNonBlankColCrow);
}