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
Selecting Cells using Excel VBA Macros - Absolute
Part four of a four-part series of blogs
This blog gives the various possible ways to select cells, ranges, columns and rows using Visual Basic for Applications in Excel. Useful for the VBA newbie, but even gurus might find one or two commands they'd missed!
This blog entry shows how to select:
If I've missed your favourite selection command out, let me know!
You can select the intersection of two ranges using Intersect. For example, a fairly odd way to select the cell containing Pooh Bear is as the intersection of row 5 and column 2:
A slightly complicated way of looking at a single cell, as the intersection of its row and column.
The code to select cell B5 would then become:
'select intersection of row 5 and column 2
Sometimes you may want to check if an intersection exists before selecting it. You can do this by testing to see if the intersection is Nothing:
'see if intersection exists, and if it doesn't display error message
If Intersect(Range("Characters"), Range("Scores")) Is Nothing Then
MsgBox "There is no intersection"
Sometimes you will want to select several cells or ranges at the same time. The easiest way to understand is to use the Union keyword:
Suppose you want to write a macro to work with the mammals in the Hundred Acre Wood only, and you need to select them first.
The code to select the above cells could be:
'select the mammals
Union(Range("B3:B5"), Range("B7"), Range("B10:B11")).Select
This is less useful than it might first seem. When you have multiple ranges selected there are limitations on what you can do with them, and coding can be quite fiddly. It will nearly always be quicker to write a macro to loop over all of the cells in a range, and use an IF condition to omit the ones of no interest (here the lagomorphs, marsupials and birds).
When you press CTRL + END in Excel, it takes you to the last used cell:
Typically the last used cell is at the bottom right of all of your data. If it's further down or further right than you might have expected, try saving your file and reopening it. If the last used cell is still further down than you'd expect, you've probably got some formatting applied to blank rows or columns at the bottom of your worksheet.
To select the last cell:
'select the last cell
It's not often that you'll need to do this, however!
That completes our tour of absolute selection using macros in Excel; now might be a good time to read my blog on relative selection in Excel VBA.
|Parts of this blog|
25 Aytoun Street