Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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.
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.
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:
rows down you need to go; and
columns to the right you need to go.
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()
'select a character's score
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.
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()
'given a character's score, select
'the character on their left
ActiveCell.Offset(0, -1).Select
End Sub
The macro above would go left one cell from the current one.
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()
'assume that we have Winnie the Pooh selected (cell B5)
'and try to go up 100 rows
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 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()
'given a character, select their score (ie go right one cell)
ACTIVECELL.OFFSET(0,1)
End Sub
The other common mistake is to get the row and column offsets the wrong way round!
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()
'select from Pooh down to bottom of block
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()
'select from Pooh down to bottom of block
ActiveCell.End(xlDown).Select
End Sub
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()
'select from Pooh down to bottom right corner of block
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.
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()
'select to bottom of block (if not already there)
'if the cell beneath the current one isn't empty ...
If ActiveCell.Offset(1, 0).Value <> "" Then
'... go down to the last cell in the block
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.
Don't forget that there are many more resources for learning Excel/VBA either at our Excel training home page or VBA home page.
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.
We've already seen the Range command once, with a single argument:
'select a cell by reference
Range("B5").Select
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()
'select the column of cells by specifying the two corners
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.
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()
'select from the current cell:
'- up to the top of the block, then down 1
'- right one cell
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 way to select a block of data is to use CurrentRegion:
Sub SelectBlockAlternative()
'push out in all directions to select a block of data
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.
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()
'given Pooh Bear as the top left corner of the world, select rest of block
ActiveCell.Range("A1:B7").Select
End Sub
You could also solve the above problem using the Resize property:
Sub ResizeBlock()
'given Pooh Bear selected initially, change height and width of selection
ActiveCell.Resize(7, 2).Select
End Sub
The arguments this property takes are:
The number of rows in the final selection; and
The number of columns in the final selection.
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()
'resize columns corresponding to current cells
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!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.