560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
Relative Selection using Excel VBA Macros Part two of a six-part series of blogs |
---|
This blog shows the various techniques within Excel Visual Basic for moving around and selecting cells relative to the active cell or selection. The blog includes a discussion of the Offset, End, Resize, Range, EntireRow, EntireColumn and CurrentRegion methods.
This blog is part of our Excel macros online tutorial series. Wise Owl also run VBA macros courses for beginners and advanced users.
|
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()
'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!
Now that we've learnt how to move around a worksheet, let's look at something slightly more complicated: ie going to the end of a block of cells. You can see far more resources on learning VBA and learning Excel by following the links given in this sentence!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.