Tutorial on Excel Macros (VBA)      

Excel VBA Consulting

VBA Excel help

Excel functions and formulas

 

Macros in Excel

 

Excel Macros Help

 

VBA for Excel Solutions

 

 

Programming in Excel

 

Macro Vocabulary in Excel

 

Forms (UserForms) in Excel

 

Moving around the Worksheet in VBA for Excel

Offset in VBA for Excel

Offset

The Offset property is the one that you will use the most with Range to move around the sheet. It is the very important property that allows you to move right, left, up and down and to extend the size of the selection.

To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select

As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns.

Knowing this you will expect an error message if you write:
Range("A1").Offset(-1,0).Select
Because there is no cell to the left of A1
You will also get an error with:
Range("B2").Offset(-2,0).Select
Because there are not 2 cells to the right of B2

If you'd try to execute any of the two lines of code above you would see this:

Here is a piece of code that you will use very frequently if you want to select one cell and 3 more down:
Range("A1" ,Range("A1").Offset(3,0)).Select
Don't forget the second closing parenthesis before ".Select".

If you want to select the cell that is already selected plus 3 mor down the code is:
Range(Activecell ,Activecell.Offset(3,0)).Select

Often you will get the following error message when you try to write such sentences:

and it is because you are forgetting the second parenthesis before Select.

For advanced users: With Offset you can also replace the numbers within the parenthesis with a number that you have stored in another cell or with a number that is in a variable:
Range("A1" ,Range("A1").Offset(myVariable,0)).Select
Range("A1" ,Range("A1").Offset(
Range("B2").Value,0)).Select
Range("A1" ,Range("A1").Offset(myVariable+6,0)).Select
See part 5 of lesson 2-6.


Learn More
At your own pace
In the confort of your home and office

 
 

Excel VBA Consulting

Excel macros help

Exxcel functions and formulas