#NiftyKeyboardShortcuts: Move between regions (CTRL + arrow keys)

I love a piece of scrap paper. A 1980's millennial, I was demographically perched on the cusp of the technological revolution that brought computing to the masses and made Uber a thing. But my schools still had blackboards; tablets were what Moses brought down from Sinai.

Others 'fell forward' and embraced the digital explosion that continues to mushroom today. I couldn't escape the comforts of nostalgia, and, 'falling back', I continue to doodle ideas 'in rough', as we used to say, on the back of old shopping lists and emails printed out for no good reason. And while moving drafts into your Trash folder is somehow dissatisfyingly clinical, the cathartic disposal of balled-up, scribbled-out jottings makes for a marvellous dopamine hit. It's a cache of evidence of industriousness, a physical reminder that ideas have to iterate and immediacy is chimerical, which my generation has been drugged out of understanding by the existence of Twitter.

The point? Excel is not a scrap piece of paper. Its remarkable flexibility makes people forget that the rows and columns exist for a reason: to organise your data. Feel free: doodle with liberality on scrap paper, but implement with precision-engineered efficiency in Excel.

There's nothing to better illustrates Excel's innate structure than its regions, between which you can jump with the super shortcut CTRL + ↓, ↑, → or ←. This combination will move you to the end of a region, defined by where the adjacent cell in the row or column is blank. Excel is telling you: "This is your dataset."

Of course, missing entries within the dataset screw up this movement, another reminder that blank rows or columns do actually mean something. If all you want to do was make data more readable and you can't handle more than a screen's worth of entries, then please, don't leave a blank row or column: stick a filter on your header row (Alt + A + T) and then (de)select what you (don't) want!

CTRL + arrow moves you around far faster than PgUp / PgDn, and it leaves using the scrollbar in the pitiful dust, although we should make an honourable mention of dragging the scroll bar itself up and down the page.

Some extra variations include CTRL + Home, which will take you back to cell A1 (unless you have used Freeze Panes, when it will return you to the cell at which you froze them). Ctrl + End will take you to the last used cell, but note that using this shortcut can often leave you beyond your current data range if you have deleted previous entries.

You can reset the last used cell by executing the command ActiveSheet.UsedRange in the VB Editor, or using the frankly weird workaround of deleting all blank rows and columns, returning to cell A1 with CRTL + Home and then saving the workbook (CTRL + S).

For any Rain Men out there, F5 + typing cell reference will take you to an exact cell that you specify, F5 having opened up the Go To dialogue box. It's pretty rare that you will know the exact cell...but maybe if you're having a phone conversation and someone tells you, "Look at cell CG378!"

---

In VBA, the End command represents the CTRL + arrow keys movements. Therefore:

ActiveCell.End(xlDown).Select

moves you to the cell at the bottom of the column your current region. You can replace xlDown with xlUp, xlToLeft and xlToRight to determine the direction of travel.

Remember that if you are already at the extreme edge of your data, executing this command takes you to the very end of the spreadsheet, which is usually a navigational mistake!

#region #navigation #end