VBA OFFSET

Excel VBA OFFSET Function

VBA Offset function is used to move or refer to a reference skipping a particular number of rows and columns, the arguments for this function in VBA is same as to the arguments in worksheet.

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 go 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.

Syntax of OFFSET in VBA Excel

• 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

Example #1

Consider the 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 the 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 the F5 key or you can run manually to select the cell A6 as shown below.

Output:

Example #2

Now take the same data but here 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. The 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 the F5 key then, it will select the cell C5 as shown in the below screenshot.

Output:

Example #3

We have seen how to offset rows and columns. 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 the F5 key or you can manually run this code then, it will select the cell A1 from A9 cell.

Output:

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, the number should be negative.
• A1 cell is the first row and first column.
• Active Cell means presently selected cells.
• 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 a downloadable template. Below are some useful excel articles related to VBA –

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