EDUCBA

Home VBA VBA Resources VBA Lookup & Reference Functions VBA OFFSET
 

VBA OFFSET

Pallavit Sharma
Article byPallavit Sharma
Madhuri Thakur
Reviewed byMadhuri Thakur

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.

Watch our Demo Courses and Videos

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

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.

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.

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