This tutorial shows the various ways to select cells using VBA within Excel in absolute mode.
You can try any of the examples contained it by downloading this Excel workbook.
Absolute vs. Relative Selection
If you're wondering at this stage what I mean by absolute and relative selection, here's an example:
Suppose you have the orange cell selected (Pooh Bear). You want to select Tigger. If you write a macro to:
I've divided the tutorial up into:
Basic selection commands.
Commands to select rows, columns and the entire worksheet.
Unusual commands (intersections of ranges, and the like).
Enjoy!
You want to go to a cell or range of cells using a macro written in Visual Basic for Applications within Excel. Here's how!
For all of the examples below, assume that you want to select the cell containing Pooh Bear (who wouldn't?).
Selecting a Single Cell
There are two ways to select a single cell. You can either do it by cell reference:
Sub SelectPoohBear()
Range("B5").Select
End Sub
Or you can do it using the row and column number:
Sub SelectPoohBear2()
Cells(5, 2).Select
End Sub
Either way works fine - the choice is up to you.
The method using Cells(row,column) can be particularly useful when looping over cells, since the row and column can be variables.
Selecting a Block of Cells
If you've got a block of cells, you can select them by cell reference or by range name:
In this case we want to select the range from B3 to B11, which also has the range name Characters.
To select a range of cells by reference, use a colon to separate the start and end cell:
Sub SelectCharacters()
Range("B3:B11").Select
End Sub
Alternatively, you could use the range name, where (as here) one exists:
Sub SelectCharacters()
Range("Characters").Select
End Sub
Now it's time to look at some less common selection commands ...
Sometimes you'll want to select entire rows or columns, or even every cell in a worksheet. Here's how:
Selecting Columns
You can do this using the Columns collection. For example:
Sub SelectColumns()
Columns("B").Select
Columns("B:D").Select
End Sub
The above macro would select column B, then columns B through to D:
The result of running the above macro.
Note that you can select a single column by number too - for example, Columns(2).Select - but this method doesn't work for a range of columns.
Selecting Rows
You can select rows in the same way, but using the Rows collection:
Sub SelectRows()
Rows("2").Select
Rows("2:4").Select
End Sub
The above routine would leave rows 2 through to 4 selected:
The results of running the macro above.
Note that as for columns you can dispense with the quotation marks if you're selecting a single row. So Rows(2).Select would also select the second row.
Selecting Every Cell in a Worksheet
In Excel you can select every single cell by clicking on the square at the top left corner of a worksheet:
Click on the square shown to select every cell in a worksheet.
The VBA equivalent command to select every single cell in a sheet is:
Having seen how to select cells, rows and columns, it's time to complete the picture by showing some of the less commonly used selection commands.
This tutorial entry shows how to select:
The intersection of two ranges;
A set of ranges which aren't necessarily next to each other; and
The last used cell in your worksheet.
If I've missed your favourite selection command out, let me know!
Selecting the Intersection of Two Ranges
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:
Intersect(Rows(5), Columns(2)).Select
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:
Sub SelectIntersection()
If Intersect(Range("Characters"), Range("Scores")) Is Nothing Then
MsgBox "There is no intersection"
Else
Intersect(Range("Characters"), Range("Scores")).Select
End If
End Sub
Selecting Several Ranges at the Same Time
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:
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).
Selecting the Last Cell in a Worksheet
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:
Sub SelectLastCell()
Range("A1").SpecialCells(xlCellTypeLastCell).Select
End Sub
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 tutorial on relative selection in Excel VBA.
