EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA OFFSET
Secondary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA OFFSET

By Pallavit SharmaPallavit Sharma

VBA OFFSET

Excel VBA OFFSET Function

As there are two things in this word, one is VBA and other is OFFSET. In this, I’ll be explaining how to use OFFSET function using VBA (Visual Basic for Applications).

VBA – It is a programming language for those who work in Excel and other Office programs, so one can automate tasks in Excel by writing Macros.

OFFSET – It is a reference function in Excel. The OFFSET function returns a reference to a range that is a specific number of rows and columns from another range or cell. It is one of the most important notions in Excel.

Let’s consider we have a dataset which consists of columns Customer Name, Product, sales, Quantity, Discount.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA OFFSET Example 1-1

Suppose on the chance that we need to move down from a particular cell to the particular number of rows and to choose that cell at that point of time OFFSET function is very useful. For example, from cell B1 we want to move down 5 cells and want to select 5th cell i.e. B6. Suppose, if you want to move down from B1 cell 2 rows and goes 2 columns to the right and select that cell i.e. cell D3.

To use OFFSET function in VBA, we have to use VBA RANGE object because OFFSET refers cells and from that RANGE object we can use OFFSET function. In Excel, RANGE refers to the range of cells.

Let’s take a look at how OFFSET is used with RANGE.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,641 ratings)

Range(“A1”).offset(5).select

How to Use the OFFSET Function in Excel VBA?

Below are the different examples to use OFFSET Function in Excel using VBA Code.

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

VBA OFFSET – Example #1

Step 1: Select the Developer Tab. Click on Insert and select the first option from ActiveX Controls. As you can see that Command Button.

Create Command Button

Step 2: Drag the arrow at any cell to create a Command Button.

Command Button 1

Step 3: To enter the OFFSET function, right-click on the Command Button and click on View Code.

VBA OFFSET Example 1-4

When you click on the View code, Microsoft VBA (Visual Basic for Applications) windows appears. In that window, we can see that some function is written.

Code:

Private Sub CommandButton1_Click()

End Sub

VBA OFFSET Example 1-5

Step 4: Inside this function, we have to write our code of OFFSET for selecting cells. As mentioned in the previously we have to use OFFSET function with RANGE in VBA.

Code:

Private Sub CommandButton1_Click()

Range(

End Sub

RANGE Function

Step 5: In this code, we have to select the 5th cell of column Product i.e. B6. Cell1 in Range is B1 because we have to move down 5 cells from cell B1 to B6 i.e 5 cells down.

Code:

Private Sub CommandButton1_Click()

Range("B1").Offset(

End Sub

VBA OFFSET Example 1-7

OFFSET function has two arguments:

  1. RowOffset: How many rows we want to move from the selected row. We have to pass the number as an argument.
  2. ColumnOffset: How many columns we want to move from the selected row.

Step 6: Now I want to select cell B6 i.e I have to move down 5 cells. So, we have to enter 5 as the parameter for Row Offset.

Code:

Private Sub CommandButton1_Click()

Range("B1").Offset(5)

End Sub

VBA OFFSET Example 1-8

Step 7: After closing the bracket we have to put a (.) dot and write the Select method.

Code:

Private Sub CommandButton1_Click()

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

End Sub

Select method

Step 8: To select the cell B6 click on the Command Button.

VBA OFFSET Example 1-10

As we can see that cell B6 gets selected after clicking on the button.

VBA OFFSET – Example #2

In this example, we will see how to use Column OFFSET argument. We will be working on the same data. All the above steps will be the same but we need to make a change in code.

Since I want to move down 5 cells and take the right 3 columns to reach the cell E6.

Code:

Private Sub CommandButton1_Click()

Range("B1").Offset(5, 3).Select

End Sub

VBA OFFSET Example 2-1

To select cell E6 click on the Command Button.

VBA OFFSET Example 2-2

As we can see that cell E6 is selected after clicking on the button.

Things to Remember

  • It is a reference function in Excel. The OFFSET function returns a reference to a range that is a specific number of rows and columns from another range or cell.
  • VBA OFFSET is used with RANGE object in VBA.

Recommended Articles

This is a guide to VBA OFFSET. Here we discuss how to use OFFSET function in Excel using VBA code along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

  1. VBA UBound
  2. OFFSET in Excel
  3. VBA Hyperlink
  4. VBA RGB
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more