WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 527 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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!

1. Selecting Cells in Excel Visual Basic Macros (Absolute)
2. Absolute Selection - the Common Excel VBA Commands
3. Selecting Rows, Columns and Entire Worksheets
4. Less Common Absolute Selection Commands (this blog)

This blog is part of our Excel macros online tutorial series.  For real-world training, book onto our introductory or advanced VBA or Excel courses.

Posted by Andy Brown on 11 August 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Less Common Absolute Selection Commands

This blog 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:

'select intersection of row 5 and column 2

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()

'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"

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:

'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).

## 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()

'select the last cell

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 blog on relative selection in Excel VBA.