- Got Sheet
- Posts
- Last Row, Like a Boss
Got Sheet is brought to you by beehiiv. If you’re considering starting a newsletter, check them out for 30 days free and 20% off your first 3 months…try beehiiv now.
From the Headlines…
I play piano. I’ve been recording small pieces and then using AI to generate longer passages from my original music. I’ve had surprisingly good results. There are a couple of these companies; Suno’s the one I’ve been playing with the most.
Last week
In the last issue we built a cool LinkedIn url utility. Read about it here.
THE LAST ROW
How do we automatically shoot our cursor down to the last empty cell? We’ll go through 3 ways to do this.
fingers + keyboard
macros + keyboard
apps script + magic
Here’s the video walkthrough:
Fingers + Keyboard
This is in many cases the fastest way to get around in a sheet.
By holding down the CTRL key and then using the arrow keys, you can shoot around in your spreadsheet like the Road Runner.
This will take your active cell in any direction ⬆️⬇️➡️⬅️ and it will jump to the last cell with data in it in that direction.
So, if you have a big dataset in the range A1:R987 and your cursor starts in A1 as the active cell, pressing CTRL + ⬇️ will shoot you down to A987.
This breaks, though, in the event that any of those cells in column A are empty. In that case, it’ll only bring you down till it hits an empty cell.
Regardless, this is my primary weapon of choice when navigating sheets.
Macros + Keyboard
A modified version of the above can be handled using Macros. These are handy if you want to record a certain set of actions that you can then repeat over and over in your spreadsheet.
You can find it up in the Extensions Menu.
Pressing record macro will then record anything you do in the sheet until you click stop.
We can then do those same actions from the first step: CTRL + ⬇️. It records that, and then we can even bind it to a keyboard shortcut
Now this is a little silly for our example, because it’s a more complicated keyboard shortcut than just hitting CTRL + ⬇️. However, it’s nice to have macros in your toolbelt for more complicated sequences.
This actually saves some code in a macros file as we’ll see in the next section…
Apps Script + Magic
Of course we’re going to write a script to do this, though. You know me, right?
And it’s incredibly simple. So simple, I’m going to show it to you first because I know you won’t be intimidated by it.
function onOpen() {
let sheet = SpreadsheetApp.getActive().getSheetByName("demo")
sheet.getRange(sheet.getLastRow()+1,1).activate()
}The first thing to note is that I’ve used an onOpen() special function for this. This executes the code as soon as our spreadsheet is opened.
The first line simply grabs the sheet we need by its name, “demo”, and holds that in the sheet variable
let sheet = SpreadsheetApp.getActive().getSheetByName("demo")
sheet.getLastRow()+1 uses that built-in function to identify the last row with data in it and add one to it.
By putting that inside of a getRange() function, we can specify the row and manually enter 1 for the column.
Finally, .activate() at the end is what puts the cursor down there and makes it the active cell.
While you’re in Apps Script, look over in the sidebar on the left…
It’s the macro we made in step 2! And it sure is a lot more complicated than our (better) apps script. 😆
function LastRow() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
};One more thing
What do you need a hand with? Today’s tips came from a question I received from a reader. Reply to this email and yours could make it into a future issue. Thanks to Kevin for this one!
Reply