#NiftyKeyboardShortcuts: Select a region (CTRL + SHIFT + arrow keys)

Selecting a region is a preparatory step in entering or transforming all the data within it. For instance, you may want to enter a figure en masse, an autofilling formula or reformat the data so that it’s all bold, or shows in £ sterling – and so on. Doing that via click and drag can be slow, especially if you’ve go an extensive dataset.

Instead, using CTRL + SHIFT + arrow keys not only moves you to the end of a region but selects all the intervening cells.

In this example, from B1, CTRL + SHIFT + → selects B1:F1. But, as my guitar teacher used to say, hold down every finger that you can, because if you keep CTRL + SHIFT pressed and hit the ↓ key, the whole selection (B1:F1) extends downwards so that it expands to B1:F6.

Note, too, that your options are more refined than just one row / column or a whole region. Keep just SHIFT held, and your selection extends by just the one cell, row or column.

This was useful recently when showing someone how to quickly change the data source of a Pivot Table. Running ants showed the existing range, A1:E18, with the highlighted extra line worth £2.3m excluded:

The fact that these lines move on the screen freaks people out; from being a merely tame refusenik, annoyingly stubborn, suddenly Excel somehow becomes a dynamic opponent, mucking stuff up rather than just sitting intimidatingly still.

But you got this. My colleague only needed to extend the data range indicated by a single row. With this dialogue box open, just hold down SHIFT and tap the down arrow. Pivot Tables updated, no problem.

To be even flashier, CTRL + A will select your entire region straight away, although needing to differentiate between headers and data makes this shortcut less frequently-useful than it first appears.

Find out more about how to complete what I call mass entries in a separate post on the keyboard shortcut CTRL + Enter.

---

For those learning VBA, you may be interested in the CurrentRegion property of a cell:

ActiveCell.CurrentRegion.Select represents CTRL + A

Tweaking the .End method covered in another Navigation post is also handy to know:

Range(Selection, Selection.End(xlToLeft)).Select

will select contiguous cells to the left in a row. See how the defined range uses two arguments separated by a comma to specify the beginning and end of the range.

Range(Selection, Selection.End(xlDown)).Select

expands that whole selection , i.e. a 1 x n array of cells, downwards

Be careful! Sometimes you will need both code lines to select an entire range, but what happens if they are both executed when the range is only one row high? For example, if I want to select the range of entries (excluding headers) in my Pivot Table data source, but there is only data on row 2…

This means that you will want to insert an If-Then structure between the lines so that the second only executes if the array is two or more cells high:

Sub Stest

Static Start as Range

Set Start = Range("A2")

Range(Start, Start.End(xlToRight)).Select

If Start.Offset(1, 0).Value <>"" Then

Range(Selection, Selection.End(xlDown)).Select)

End If

End Sub

#navigation #selection #end #region