564 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
Relative Selection using Excel VBA Macros
Part five 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.
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:
'given Pooh Bear as the top left corner of the world, select rest of block
You could also solve the above problem using the Resize property:
'given Pooh Bear selected initially, change height and width of selection
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!
|Parts of this blog|
25 Aytoun Street