WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA OFFSET

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

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
  • VBA Set
  • What is OFFSET Formula in Excel?
  • VBA Cells References
  • VBA Format Date
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA OFFSET Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More