Excel Tip : Ctrl And Arrow Keys
One of the keyboard shortcuts that I use most often in Excel allows you to quickly move around big spreadsheets by jumping to the start, end, top or bottom of the table.
It is also the basis for some of the most repeated VBA Macro code snippets that I use.
There are 8 shortcuts
- Ctrl + left/right arrow : jumps to the left/rightmost cell of your data region
- Ctrl + up/down arrow : jumps to the top/bottom of your data region
- Ctrl + Shift + left/right arrow : selects from the current cell across to the left/rightmost cell of your data region
- Ctrl + Shift + up/down arrow : selects from the current cell up/down to the top/bottom of your data region.
The table below will be used to demonstrate these shortcuts.
Note that column E is longer than the others and also that cell E3 is blank. This has been done on purpose.
Recreate a similar table yourself with any numbers/text, the content is not important, but make column E longer and clear the contents of E3.
Ctrl + Left/Right Arrow
Select cell A1, press the Ctrl key and Right Arrow keys together.
Excel jumps from A1 across to H1, the rightmost cell in our data region.
You could select cell F1 and do the same thing, H1 is the rightmost cell in this data region.
Press the Ctrl and Left Arrow keys again and it will jump back to A1, the leftmost cell in the data region.
This time, select cell A3 and press Ctrl and Right Arrow keys together.
This time, Excel goes from A3 across to D3. This is because D3 is the rightmost cell in our data region. There are more cells to the right, but the blank E3 seprates them.
Press Ctrl and Right Arrow again and Excel moves to F3, the next rightmost point in the data region.
Press Ctrl and Right Arrow again and Excel moves to H3, the next rightmost point in this data region.
Code Snippet
The VBA code snippets for Ctrl+Left/Right Arrows are:
- Selection.End(xlToRight).Select
- Selection.End(xlToLeft).Select
Ctrl + Up/Down Arrow
The Ctrl + Left/Right Arrow shortcuts jump to the leftmost or rightmost cell of your data region, so by now you have probably guessed that Ctrl + Up/Down will move to the top or bottom of your data region.
Select A1 and press the Ctrl and Down Arrow keys together.
Pressing Ctrl and Up Arrow keys together will take you back to A1 again.
Now select E1 and press Ctrl and Down Arrow, this only goes down to E2, the bottom of the current data region.
Press Ctrl and Down again goes to E4, and Ctrl and Down again goes to E18.
Code Snippet
- Selection.End(xlDown).Select
- Selection.End(xlUp).Select
Ctrl + Shift + Left/Right/Up/Down Arrows
By pressing the Shift key at the same time as Ctrl and an Arrow key, you select from the current cell to the leftmost/rightmost/top/bottom cell in your data region and everything in between.
Select cell A1, press the Ctrl, Shift and Right Arrow keys together. Excel jumps to H1, but selects all cells between A1 and H1, inclusive.
Code Snippet
- Range(Selection, Selection.End(xlToRight)).Select
- Range(Selection, Selection.End(xlToLeft)).Select
- Range(Selection, Selection.End(xlDown)).Select
- Range(Selection, Selection.End(xlUp)).Select
Ctrl & Arrow Keys Uses
When working with large files using the Ctrl and Arrow keys is a quick way to move around files. How many times have you scrolled down through a long file and went too far, only to have to scroll back up again. Ctrl and Down would move to the bottom of the data region quickly.
I use these keys in combination with other keys to select cells for copying/pasting/summing/etc.
When creating VBA macros, this shortcut is an easy way to find the bottom row of a table for summing or other calculations where the number of rows or columns changes everytime so hardcoding cell references won’t work.
[grwebform url=”http://app.getresponse.com/view_webform.js?wid=2766903&u=CucD” css=”on”/]
