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.

OFFSET Data

Now from cell A1, you want to move down 4 cells, and you want to select that 5th 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 objectVBA RANGE ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more first, and from that range object, we can use the OFFSET property.

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

Syntax of OFFSET in VBA Excel

OFFSET Formula
  • 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 A,1, i.e., Range (“A1”).

Examples

You can download this VBA OFFSET Template here – VBA OFFSET Template

Example #1

Consider the below data, for example, demonstration.

OFFSET Example 1

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
VBA OFFSET Example 1-1

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
VBA OFFSET Example 1-2

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
VBA OFFSET Example 1-3

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

VBA OFFSET Example 1-4

Output:

VBA OFFSET Example 1-5

Example #2

Now take the same data but here will see how to use the 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
VBA OFFSET Example 2

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

VBA OFFSET Example 2-1

Output:

VBA OFFSET Example 2-2

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
Negative Number Example 1

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.

Negative Number Example 1-1

Output:

Negative Number Example 1-2

Example #4

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

From the active cell, l, i.e., C8 cell, we need to first move down 2 rows, and we need to move to the 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
Negative number Example 2

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

Negative number Example 2-1

Output:

Negative number Example 2-2

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 to mention “.Select.”
  • If you want to copy the cell using OFFSET, you need to mention “.Copy.”

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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>