Excel VBA Offset Column (5 Suitable Cases)

Often we need to move the active cell to the next cell without explicitly specifying its row and column coordinates. The Offset function provides a very convenient way to do this. In this article, we will discuss the use of the Offset function, more specifically in cases of Column offset in Excel VBA. We will explain the syntax of the Offset property in different cases like selecting a single column, single cell, multiple columns, assigning values to cells, and so on.

The Offset column property is very useful in copying cell entries from a column. The above image describes this fact. You may use the code to copy the required data from your dataset.

How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to launch the VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to display the Developer tab on your ribbon.

• First, we go to the Developer tab.
• Then we will select Visual Basic.

Then a new window will pop up. Then we are going to follow these steps.

• First, we will select Insert.
• Then we are going to select Module.
• A new Module will be created.

Syntax of Offset in Excel VBA

The Offset function is a built-in function in Excel VBA that refers to a cell or range of cells that are a specified number of rows and columns away from a starting cell or range. The Syntax of the Offset function is:

Excel VBA to Offset Column: 5 Suitable Cases

1. Offset Column and Select Cell by Activecell.Offset Property

ActiveCell.Offset is a method in VBA that is used to refer to a cell that is a specified number of rows and/or columns away from the currently active cell. The ActiveCell object represents the currently active cell in the active worksheet. By using the Offset method with the ActiveCell object, we can easily refer to cells that are nearby without specifying their row and column numbers explicitly.

1.1  Move Right of Column

In order to move right to the current cell, we will define the row offset as zero and give the column offset value as required. The following is a code that moves the current selection to the right in VBA.

Sub offset_active()
   ActiveCell.Offset(0, 1).Select
End Sub

If you write the code in VBA Editor, it will look like the image below.

ActiveCell.Offset(0, 1).Select

Execution of Code & Viewing Output

To view the output, we first select a cell in the worksheet and then we run the code above in VBA. Before running the code, the active cell is B10.

Just after we run the code, C10 becomes the active cell.

1.2  Move Specific Number of Cells from Column

In the above example, we moved the cell just one column right and it was fixed. There was no option to move the desired number of columns or no InputBox option available for the user. In the following example, the user will give an input and the code will move the current selection to that specific desired cell. The code is like below.

Sub Offset_column_cell()
Dim offsetAmount As Integer
offsetAmount = InputBox("Enter the number of columns to offset:")
ActiveCell.Offset(0, offsetAmount).Select
End Sub

When we write the code in the VBA Editor, we find the editor like the image below.

offsetAmount = InputBox("Enter the number of columns to offset:")

ActiveCell.Offset(0, offsetAmount).Select

Execution of Code & Viewing Output

In order to view the output, we run the code. An InputBox appears like the image below.

After hitting Enter on your keyboard, we get the final output like the one below.

2. Offset Column Using Range.Offset Property

Like the ActiveCell.Offset property, we can use the Range.Offset property in VBA to offset cells. Range.Offset property serves the same purpose as that of ActiveCell.Offset property. In the following example, we will use the property to offset cells in VBA. The following code will serve the purpose.

Sub Offset_rangenext()
  Range("d9").Offset(, -2).Select
End Sub

The code in the VBA Editor takes the following look.

  Range("d9").Offset(, -2).Select

Execution of Code & Viewing Output

In the case of ActiveCell.Offset method, we have to select a cell first then we run the code. But the Range.Offset property doesn’t require selecting the cell first. We just run the code and get the output. In this case, the B9 cell is selected in the output.

3. Offset Whole Column Selection in VBA

We can use the Excel VBA Offset function not only for cells but also for a whole column selection. Sometimes it may be very helpful. In the following code, we will do the same thing.

Sub Offset_selectarea()
Dim selectedRange As Range
Dim offsetAmount As Integer
' Get the selected range of cells
Set selectedRange = Selection
' Get the amount of offset from the user
offsetAmount = InputBox("Enter the number of columns to offset", "Offset Columns")
' Offset the selected range
selectedRange.Offset(0, offsetAmount).Select ' Select the offset cells
End Sub

After writing the code in the Editor, it looks like the image below.

Set selectedRange = Selection

selectedRange.Offset(0, offsetAmount).Select

Execution of Code and Output Viewing

To view the output, we have to select a column first.

After that, we select the Macro and run the code. In the InputBox, we put the value “3”.

The final output looks like the image below. In this way, we can use Excel VBA Offset to select the whole column.

4. Offset Property to Assign Value to Several Columns

Using the offset property, we can assign values to several columns in the Excel sheet. This method is convenient especially when we assign numerical values to the cells. In case of assigning other values to the cell, we will convert the values into an array. The following example will assign values to the 2nd, 3rd, 4th, 5th, and 6th columns. It is possible to assign values to more cells by modifying the code below.

Sub AssignValues()
Dim myValues As Variant
myValues = Array("Apple", "Banana", "Orange", "Pear", "Grape")
Range("B5").Select
'Loop through each value in the array
For i = 0 To UBound(myValues)
    'Assign the current value to the next cell in the active row
    ActiveCell.Offset(-1, i).Value = i + 1
    ActiveCell.Offset(0, i).Value = myValues(i)
    Next i
End Sub

If you write the code in the VBA Editor, it will look like the image below.

myValues = Array("Apple", "Banana", "Orange", "Pear", "Grape")

For i = 0 To UBound(myValues)

ActiveCell.Offset(-1, i).Value = i + 1

ActiveCell.Offset(0, i).Value = myValues(i)

Execution & Output

In order to see the output, we run the code and find the output like the image below.

5. Copying Column Range Using Offset

The Offset function is a great feature of Excel VBA to copy the values of a column and use those values later. In the following section, we will give an example of the code to do this.

Sub Copy_Column_values()
Dim rng As Range
Dim val1 As String
Set rng = Range(Range("A4").Offset(1, 1), Range("A11").Offset(1, 1))
'Copying the range values to clipboard
rng.Copy
'Loop through each cell in the range
For Each Cell In rng
       'Storing the values in string
        val1 = val1 & Cell.Value & vbCrLf
Next Cell
'Display the string in a message box
MsgBox "The copied values are:" & vbCrLf & val1
End Sub

VBA Editor with the code looks like the image below.

Set rng = Range(Range("A4").Offset(1, 1), Range("A11").Offset(1, 1))

Range("A4").Offset(1, 1)

Range("A11").Offset(1, 1)

Set rng

rng.Copy

For Each Cell In rng

val1 = val1 & Cell.Value & vbCrLf

Execution & Output

To view the output of the code, we run it from the Developer tab. When we click on the Run button, we see the output like below.

How to Use Range Offset in Excel VBA

In all the examples above, we saw the column offset only. But in the following example, we will see how to offset a selected range to 2 rows down and 3 columns right (or any amount of that matter, by modifying the code). The code to do this is as follows.

Sub Range_offset()
Dim selectedRange As Range
Set selectedRange = Application.InputBox("Select a range:", Type:=8)
If selectedRange Is Nothing Then
    MsgBox "You did not select a range."
Else
    selectedRange.Offset(2, 3).Select
End If
End Sub

After writing the code in the editor, we find the editor looks like the image below.

Set selectedRange = Application.InputBox("Select a range:", Type:=8)

If selectedRange Is Nothing Then

selectedRange.Offset(2, 3).Select

Execution and Output

When we run the code above in VBA, an InputBox appears. We select the range and the code takes it for further operations.

If we press “OK” in the Msgbox, it shows the following output.

How to Use Offset Within a Loop in Excel VBA

It is possible to combine the Offset function with For loop in VBA code. In the following example, we will use a combination of these two. In this example, we have a dataset of numbers of some students and we will find out their total marks using the code.

Using the following code, we will find out the total marks and assign them to column “E”.

Sub Offset_loop()
Dim Row As Integer
Dim i As Integer
Dim num1 As Double
Dim num2 As Double
Row = Range("B" & Rows.Count).End(xlUp).Row ' Find the last row of the data
For i = 5 To Row ' Loop through each row of the data, starting at row 5
        num1 = Range("C" & i).Value
        num2 = Range("D" & i).Value
        Range("D" & i).Offset(0, 1).Value = num1 + num2
    Next i
End Sub

The above code in the Editor looks like the image below.

Row = Range("B" & Rows.Count).End(xlUp).Row 

Execution & Output

To view the output, we run the code by the method mentioned above. After that, we get the output like the image below.

Read More: Excel VBA Offset Within Loop

Frequently Asked Questions (FAQs)

• What is the Offset function in Excel VBA?

The Offset function in Excel VBA allows you to move the active cell or a range of cells a certain number of rows and columns from its current location.

• How do you use the Offset function to move to a specific column?

To move to a specific column, you can use the Offset function with a Rows argument of 0 and a Columns argument that corresponds to the number of columns you want to move. For example, if you want to move to the column 5 columns to the right of the active cell, you can use the following code:

ActiveCell.Offset(0, 5).Select

• How do you use the Offset function to reference a range of cells in a specific column?

To reference a range of cells in a specific column, you can use the Offset function in combination with the Range function.

Things to Remember

• The syntax for the Excel VBA Offset function is Offset(Reference, Rows, Columns, Height, Width).
• The Reference argument is the starting cell or range of cells.
• The Height and Width arguments are optional and specify the size of the range in output.
• The Offset function can be a powerful tool for manipulating data in Excel VBA, but it should be used with care to avoid errors and unexpected results.

Download Practice Workbook

You may download the following workbook to practice yourself.

Conclusion

That is the end of this article regarding the Excel VBA Offset column. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.