Another tutorial has already explained how to select cells in absolute mode; this one shows how to select cells relative to your starting point. Examples of relative selection would be:
Go down 3 cells;
Go to the end of the current block of cells;
Select from this cell down to the bottom of the block.
If you want to try out any of the macros listed in this tutorial, you can download the file of Winnie the Pooh characters.
ActiveCell and Selection
Before we begin, there are two keywords which you must understand in Excel Visual Basic for Applications (VBA): ActiveCell and Selection.
Here the active cell is B5, containing Pooh Bear, and the selection is B5:C11 as shown ... selected!
Every worksheet has an active cell, and when only one cell is selected, the active cell and selection are the same thing.
Now that we've learnt about the two key Excel VBA concepts of ActiveCell and Selection, let's look at how you can move round a spreadsheet.
Moving Round using Offset
To move round a spreadsheet, use the Offset property. Let's suppose that you want to go from any character to their Poohsticks score in the example below:
With a character selected initially, you want to write a macro to select their score (ie the cell immediately to the right).
The Offset property which you need always specifies how many:
In our case, we want to go 0 cells down and 1 column to the right:
All movement using Offset is down and to the right.
If you forget which order the directions come in, use autocompletion:
When you type in the Offset property, it's obvious that rows come before columns.
Thus the full command for our example would be:
Sub SelectScore()
ActiveCell.Offset(0, 1).Select
End Sub
That is, take the current cell, move it 0 rows down and 1 column to the right, and select the result.
Moving Up and Left
If you've been reading thus far, you'll be wondering how you can possibly go up and to the left. The answer is to use negative numbers as the row and/or column offsets:
Sub SelectScore()
ActiveCell.Offset(0, -1).Select
End Sub
The macro above would go left one cell from the current one.
Overshooting the Edge of the Worksheet
If you try going too far in one direction, you'll generate an error. For example, suppose you're on the Winnie the Pooh cell and run this macro:
Sub SelectScore()
ActiveCell.Offset(-100, 0).Select
End Sub
The result will be a less-than-friendly error message:
Microsoft politely telling you that you've gone beyond the edge of a worksheet
The Most Common Errors using Offset
The most common mistake using Offset is to forget to select the results of an offset (in the code below, we need to add .Select to the end of the command in CAPITALS, to complete the sentence):
Sub SelectScore()
ACTIVECELL.OFFSET(0,1)
End Sub
The other common mistake is to get the row and column offsets the wrong way round!
Going to the End of a Block
Suppose now that you want to select from Winnie the Pooh down to the last character in the Hundred Acre Wood:
We want to select from the green cell all the way down to the bottom of the block.
Clearly one way to do this would be to offset 6 cells down:
Sub SelectLastCharacter()
ActiveCell.Offset(6, 0).Select
End Sub
The problem with this approach is that it wouldn't work if we had a different character selected, nor if we add more characters to the list. What we need to do is to go to the last non-blank cell in the block. Fortunately, that's exactly what the End property does:
From any cell you can move in one of 4 directions: up, down, left and right.
When you start typing in a command, Excel will suggest the 4 possible directions:
The 4 possible directions for END
Thus for our example, the line of code you'll need is:
Sub SelectLastCharacter()
ActiveCell.End(xlDown).Select
End Sub
Combining END Properties
If you add one END property on to the other, it can be a quick method of getting to the bottom right corner of a block:
Sub SelectLastCharacterScore()
ActiveCell.End(xlDown).End(xlToRight).Select
End Sub
Thus if you select Pooh Bear's cell B5 and then run the above macro, you'll get the following:
Beginning with the green cell selected, you'll end up with the orange one, following the arrows.
What Happens if you're at the End of a Block
One thing to watch out for is that you might be at the end of a block when you run an End command. If this is the case, expect the unexpected!
 |  |
If you start here and go down to the end of a block ... | ... don't be surprised if you end up on the last row of your sheet! |
To get round this, you could consider checking the cell beneath before you go to the end of your block:
Sub SelectLastCharacter()
If ActiveCell.Offset(1, 0).Value <> "" Then
ActiveCell.End(xlDown).Select
End If
End Sub
The above macro will have a quick peek at the cell below the current one, and only when it's sure that this cell is non-empty will it go to the bottom of the block.
A common requirement is to select the block of cells from the currently selected cell down:
 |  |
Given Pooh Bear selected ... | ... we want to select the block |
This then allows you to loop over all of the cells selected (perhaps to process the scores for each character?).
The macro shown above is the VBA equivalent of holding the SHIFT key down while you press CTRL + DOWN ARROW.
The Range Command with Two Arguments
We've already seen the Range command once, with a single argument:
However, you can also use this command with two arguments, in which case VBA will take these to be the corners of the rectangle of cells that you want to select.
In our example, we want to select from the currently selected character (let's say this is Pooh Bear) down to the bottom of the block:
We want to select the block of cells with the two corners shown in green (ie from B5 down to B11).
To achieve this, we need a command like this:
Sub SelectBlock()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
If you run this command with B5 selected, you'll get the following:
The results of running the macro above - VBA has selected the rectangle of cells with B5 as one corner and the bottom cell in the block as the other.
Another Example of using Range to Select Blocks of Data
Suppose that we have Tigger selected, and want to select the block of data shown in orange:
Given that we've got cell B11 selected, we want to select up and to the right to select the orange area. A complication is that we want to exclude the titles row.
A macro to achieve this could be:
Sub SelectBlock()
Range( _
ActiveCell.End(xlUp).Offset(1, 0), _
ActiveCell.Offset(0, 1)).Select
End Sub
Notice how you can move each of the corners of the rectangle to anywhere you want on screen - provided that you specify two valid corners, VBA will always select the block of data they mark out.
An Alternative - the CurrentRegion
An alternative way to select a block of data is to use CurrentRegion:
Sub SelectBlockAlternative()
ActiveCell.CurrentRegion.Select
End Sub
This method will select the entire block of cells of which the active cell is a part:
 |  |
Given the green active cell ... | ... the above macro selects the block |
While the above looks temptingly simple, it suffers from two problems:
The macro will select the titles too - there's no easy way to omit these using CurrentRegion.
The macro may select more than you expect (see example below).
To illustrate the second point, supposing that you have typed a space into any of the cells shown shaded below:
The CurrentRegion method keeps going until it finds completely blank rows and columns. Woe betide you if there's some hidden content in the cells shown shaded here!
Because of the unpredictability of CurrentRegion, we'd recommend avoiding it and using the more complicated Range technique shown above.
Using the Range Property within a Command to Resize
You can use either the Range property (within a command) or the Resize property to change the height and width of blocks of cells.
These techniques aren't that commonly used, so if you've read thus far and are feeling proud of yourself but a little tired, now's a good time to stop!
The way this method works is by redefining the coordinate system temporarily to make the active cell the top left corner. This is easier to explain in diagrams!
Suppose that with Pooh Bear (inevitably) selected, we want to select the block of cells shown:
We want to resize the green cell so that it begins on B5, and carries on down and across to C11.
If you consider the Pooh Bear cell to be the top left corner of another virtual spreadsheet, we actually want to select range A1:B7:
If the Pooh Bear cell were cell A1, then the area we'd be selecting is shown shaded.
Given this rather odd way of looking at the world (or at least, at the Excel part within it), the command to make this work is as follows:
Sub ResizeBlock()
ActiveCell.Range("A1:B7").Select
End Sub
Using Resize to Change the Height and Width of a Block of Cells
You could also solve the above problem using the Resize property:
Sub ResizeBlock()
ActiveCell.Resize(7, 2).Select
End Sub
The arguments this property takes are:
While conceptually this is fairly easy to understand, it's hard to think when it might be a useful command to run!
The EntireColumn and EntireRow properties allow you to turn a block of cells into the columns or rows containing them.
The macros in this tutorial entry mimic the short-cut keys SHIFT + SPACE BAR (to select rows) and CTRL + SPACE BAR (to select columns).
As an example, suppose that you wanted to use a macro to automatically resize columns B and C below to accommodate the widest text in them:
 |  |
We want to go from this ... | ... to this |
The command to do this could be as follows:
Sub ChangeColumnWidths()
Selection.EntireColumn.AutoFit
End Sub
You could use a similar method to resize the rows corresponding to the current selection.
If you can think of any other relative selection or absolute selection techniques I've missed out, let me know. Otherwise, happy coding!
