Wednesday, August 22, 2012

Scroll down to last row

Scroll down to last row by default

If you are having a huge amount of rows in your spreadsheet, then you must be wondering how to navigate to the last row immediately.

So here is the script which will do your task:


function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"MyFunction", functionName:"myFunction"}];
  sheet.addMenu("Scripts", entries);
};

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  var lastrow = mysheet.getLastRow();
   
  mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
};


You can also navigate to your desired row, here is the script for it:
http://igoogledrive.blogspot.in/2012/08/spreadsheet-quick-scroll-down-to.html

You can also navigate to your last modified cell, here is the script for it:
http://igoogledrive.blogspot.in/2012/08/how-to-get-location-of-last-modified.html 


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

13 comments:

  1. Is there a way to have this script run automatically, so I don't have to train everyone who opens the doc to click on "Scripts" and then click on "myFunction"? I would like to be able to open the doc and have it automatically show the bottom row. Thanks!

    ReplyDelete
    Replies
    1. Do This;

      function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var mysheet = ss.getActiveSheet();

      var lastrow = mysheet.getLastRow();

      mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
      };

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Coolshrimp Modz!, you save my live!!!
      Thanks!!!

      Delete
    4. Coolshrimp Modz, your script is the only one that I can work on my spreadsheet. Thank you for the info.
      Do you have a script for scrolling to the first empty row?

      Delete
  2. What add on do I need to run this script. Seems newer versions of Sheets doesn't use the Scripts any more

    ReplyDelete
    Replies
    1. I just used a modified version of the original script posted above, and it works fine with current version. The original script is more complicated that necessary - it creates a menu option to go to the last line, when the op request was for it to happen automatically:

      function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var mysheet = ss.getActiveSheet();

      var lastrow = mysheet.getLastRow();

      mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
      };

      Delete
  3. This does work but not when you have conditional formatting and dropdowns on the form. I've prepped 2000 lines with these formulas i need and this jumps to after thatbut i'm only on line 550 so pointless. I'll stick to usign ctrl and arrow down.
    Thanks

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hi Kishan,

    nice script, could you please advise how to epxand it so in case of multiple sheets are in the workbook, all of them will be at the last row after clicked on?

    Thanks, Lukas

    ReplyDelete