Find LastRow of column C (when Col A and B have different row size)?
How do I find the last used cell in column C?
Example: "Sheet1": "Col A" and "Col B" have 1200 lines. And "Col C" only has 1 line.
## ColA ColB ColC ## 1 1 1 ## 2 2 empty ## .. .. .. ## 1200 1200 empty
Here are my failed 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 cannot use C1 because the next time I use the function it will be C1200 or whatever.
var lastRow = ws_sheet.getRange("C1").getLastRow();
I ask this because my next goal is to copy / paste the result of C1 to C2: C1200. Here's 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);
Thank you in advance;)
ps: I spent 2 hours on this. I have tried similar problems and solutions already presented on this website, but I cannot get them to work. I'm lost !: More efficient way to find the last row in a specific column? and Get the last row of a specific column function is the best solution
source to share
As I mentioned in the comments above, this is the posting topic with the highest score on StackOverFlow ...
The original post returns the value of the last cell in the column, but a (very) slight 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 the index of the last used cell in the 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;
}
source to share
[UPADTE} Please ignore this answer. Instead of the user code Serge. I had a brain fart. His answer is all the better. This will teach me not to answer SO questions after you return from a cocktail night ... [/ UPDATE]
The following function will write the last empty row number of column C. Note: If, for example, column C has a value in row 1 and row 200, and rows 2-199 are empty, the function returns 200 as the last non-empty row - it does not count empty rows over the last nonblank line.
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);
}
source to share
Here's a 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 call it like =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 correctly access empty columns and also start counting down from the last row with content on the active sheet getLastRow()
, rather than from the last row on the sheet (with or without content) getMaxRows()
as in the accepted answer.
source to share