This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Selecting Cells in Excel Visual Basic Macros (Absolute) Absolute Selection - the Common Excel VBA Commands Selecting Rows, Columns and Entire Worksheets 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.

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!

Selecting Cells in Excel Visual Basic Macros (Absolute)
Absolute Selection - the Common Excel VBA Commands
Selecting Rows, Columns and Entire Worksheets
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

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.

Parts of this blog
Selecting Cells in Excel Visual Basic Macros (Absolute) Absolute Selection - the Common Excel VBA Commands Selecting Rows, Columns and Entire Worksheets Less Common Absolute Selection Commands (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC