Go to last line of data in Google Sheets

user_6091091 0
Viewed 7764

I have poked around and found the following code that will advance to the last line on data in our Google Spreadsheet- at least until we add more lines beyond row 297.

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getSheets()[0].getRange("B297");
   SpreadsheetApp.setActiveRange(range);
}

I am trying to figure out how to write this script so that it will go to the last line of data, regardless of the line number.

Is there a change that I can make to this code to accomplish this?

google-apps-script google-sheets
2 Answers

The script below allows you to go to the last cell with the content of column A. It works even if some cells in the column A contain formulas.

Modifying the number in parentheses in lastRowOfCol(1) allows you to reach the last cell with content from another column.

Additionally, you can also change the focus to the first empty cell after the last one with content.

function onOpen(){
  lastRowOfCol(1); //Enter the column number you want to use as the base of the search
}

function lastRowOfCol(column){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var total = sheet.getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(1,column,total).getValues();
  for(var i=total-1;values[i]=="" && i>0; i--){}
  var last = sheet.getRange(i+1,column);
  //var last = sheet.getRange(i+1,1); //Option to fetch the last row of a given column, but to position in column 1
  //var last = sheet.getRange(i+2,column); //Option to go to the cell below the last one with content 
  sheet.setActiveSelection(last);
}

Script from Marcelo Camargo in this forum

user_90739780
Related