This page has 0 threads | Add post

Blogs

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

Relative Selection using Excel VBA Macros Part two 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. Selecting Cells with Excel VBA Macros - Relative Moving Around a Spreadsheet in VBA - Offset (this blog) Selecting to the End of a Block Selecting a Block of Cells using Range Resizing Blocks of Cells in Excel VBA macros Selecting Entire Columns or Rows in Excel VBA macros This blog is part of our Excel macros online tutorial series. Wise Owl also run VBA macros courses for beginners and advanced users.

Relative Selection using Excel VBA Macros

Part two 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.

Selecting Cells with Excel VBA Macros - Relative
Moving Around a Spreadsheet in VBA - Offset (this blog)
Selecting to the End of a Block
Selecting a Block of Cells using Range
Resizing Blocks of Cells in Excel VBA macros
Selecting Entire Columns or Rows in Excel VBA macros

This blog is part of our Excel macros online tutorial series. Wise Owl also run VBA macros courses for beginners and advanced users.

Posted by Andy Brown on 16 August 2011

Moving Around a Spreadsheet in VBA - Offset

To move round a spreadsheet, use the Offset property. Let's suppose that you want to go from any character to their Poohsticks score in the example below:

With a character selected initially, you want to write a macro to select their score (ie the cell immediately to the right).

The Offset property which you need always specifies how many:

rows down you need to go; and
columns to the right you need to go.

In our case, we want to go 0 cells down and 1 column to the right:

All movement using Offset is down and to the right.

If you forget which order the directions come in, use autocompletion:

When you type in the Offset property, it's obvious that rows come before columns.

Thus the full command for our example would be:

Sub SelectScore()

'select a character's score

ActiveCell.Offset(0, 1).Select

End Sub

That is, take the current cell, move it 0 rows down and 1 column to the right, and select the result.

Moving Up and Left

If you've been reading thus far, you'll be wondering how you can possibly go up and to the left. The answer is to use negative numbers as the row and/or column offsets:

Sub SelectScore()

'given a character's score, select

'the character on their left

ActiveCell.Offset(0, -1).Select

End Sub

The macro above would go left one cell from the current one.

Overshooting the Edge of the Worksheet

If you try going too far in one direction, you'll generate an error. For example, suppose you're on the Winnie the Pooh cell and run this macro:

Sub SelectScore()

'assume that we have Winnie the Pooh selected (cell B5)

'and try to go up 100 rows

ActiveCell.Offset(-100, 0).Select

End Sub

The result will be a less-than-friendly error message:

Microsoft politely telling you that you've gone beyond the edge of a worksheet

The Most Common Errors using Offset

The most common mistake using Offset is to forget to select the results of an offset (in the code below, we need to add .Select to the end of the command in CAPITALS, to complete the sentence):

Sub SelectScore()

'given a character, select their score (ie go right one cell)

ACTIVECELL.OFFSET(0,1)

End Sub

The other common mistake is to get the row and column offsets the wrong way round!

Now that we've learnt how to move around a worksheet, let's look at something slightly more complicated: ie going to the end of a block of cells. You can see far more resources on learning VBA and learning Excel by following the links given in this sentence!

Parts of this blog
Selecting Cells with Excel VBA Macros - Relative Moving Around a Spreadsheet in VBA - Offset (this blog) Selecting to the End of a Block Selecting a Block of Cells using Range Resizing Blocks of Cells in Excel VBA macros Selecting Entire Columns or Rows in Excel VBA macros

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

BLOGS BY AUTHOR

Moving Around a Spreadsheet in VBA - Offset

Moving Up and Left

Overshooting the Edge of the Worksheet

The Most Common Errors using Offset

Head office

London

Manchester