VBA OFFSET

VBA OFFSET Function

VBA OFFSET is one of the important concepts in excel. If you want to move down from a specific cell to the specific number of rows and to select that cell then offset method in excel is very helpful.

For example assume you have a set of data like the below one.

Now from cell A1, you want to move down 4 cells and you want to select that 5^th cell i.e. A5 cell.

Similarly, if you want to move down from A1 cell 2 rows and goes 2 columns to the right and select that cell i.e. C2 cell.

In these cases, the OFFSET function is very helpful. Especially in VBA OFFSET Function is just phenomenal.

OFFSET is Used with Range Object in Excel VBA

In VBA we cannot directly enter the word OFFSET. We need to use the VBA RANGE object first and from that range object, we can use OFFSET property.

In excel range is nothing but a cell or range of the cell. Since OFFSET refers cells we need to use the object RANGE first and then we can use the OFFSET method.

Formula of OFFSET in Excel VBA

Now take a look at the Formula of OFFSET property now.

• Row Offset: How many rows you want to offset from the selected cell. Here selected cell is A1 i.e. Range (“A1”).
• Column Offset: How many columns you want to offset from the selected cell. Here selected cell is A1 i.e. Range (“A1”).

Examples of OFFSET Function in Excel VBA

Following are the examples of OFFSET Function in Excel VBA.

VBA OFFSET Function Example #1

Let’s take look at the simple example of using VBA OFFSET. Consider below data for example demonstration.

Now I want to select the cell A6 from the cell A1. Start the macro and reference cell using the Range object.

Code:

Sub Offset_Example1()

    Range("A1").offset(

End Sub

Now I want to select the cell A6 i.e. I want to do down 5 cells. So enter 5 as the parameter for Row Offset.

Code:

Sub Offset_Example1()

    Range("A1").offset(5

End Sub

Since I am selecting in the same column I leave out column part. Close the bracket and put a dot (.) and type the method “Select”.

Code:

Sub Offset_Example1()

    Range("A1").Offset(5).Select

End Sub

Now run this code using F5 key or you can run manually to select the cell A6 as shown below.

Output:

VBA OFFSET Function Example #2

Now take the same data but this will see how to use column offset argument as well. Now I want to select the cell C5.

Since I want to select the cell C5 firstly I want to move down 4 cells and take the right 2 columns to reach the cell C5. Below code would do the job for me.

Code:

Sub Offset_Example2()

    Range("A1").Offset(4, 2).Select

End Sub

I run this code manually or using F5 key then, it will select the cell C5 as shown in below screenshot.

Output:

VBA OFFSET Function Example #3

We have seen how to offset rows and column. We can also select the above cells from the specified cells as well. For example, if you are in the cell A10 and you want to select A1 cell, how do you select?

In case of moving down the cell we can enter a positive number, so here in case of moving up, we need to enter negative numbers.

From A9 cell we need to move up by 8 rows i.e. -8.

Code:

Sub Offset_Example1()

    Range("A9").Offset(-8).Select

End Sub

If you run this code using F5 key or you can manually run this code then, it will select the cell A1 from A9 cell.

Output:

VBA OFFSET Function Example #4

Assume you are in the cell C8. From this cell, you want to select the cell A10.

From the active cell i.e. C8 cell we need to first move down 2 rows and we need to move to left by 2 columns to select the cell A10.

In case of moving left to select the column, we need to specify the number is negative. So here we need to come back by -2 columns.

Code:

Sub Offset_Example2()

    Range("C8").Offset(2, -2).Select

End Sub

Now run this code using F5 key or run manually, it will select A10 cell as shown below:

Output:

Things to Remember

• In case of moving up of rows, we need to specify the number in negatives.
• In case of moving left to select the column number should be negative.
• A1 cell is the first row and first column.
• Active Cell means presently selected cell.
• If you want to select the cell using OFFSET you need mention “.Select”.
• If you want to copy the cell using OFFSET you need mention “.Copy”.

Recommended Articles

This has been a guide to VBA OFFSET. Here we learn how to use VBA OFFSET Property to navigate in Excel along with practical examples and downloadable template. Below are some useful excel articles related to VBA –

• DIR Function in VBA
• VBA Set
• OFFSET Formula in Excel
• VBA Cells
• VBA FIND Function
• VBA MsgBox
• How to handle Arrays in VBA