How to Use VBA Range Offset (11 Easy Ways)

If you are looking for some of the easiest ways to use VBA Range Offset, then you will find this article worth it. Let’s get started with the ways of using the VBA Range Offset. Before that, take a quick look at the overview below.

11 Suitable Ways to Use VBA Range Offset

I have the following data table containing some students’ information about a college. Using this dataset, I will explain the ways of using the VBA Range Offset.

For this purpose, I have used Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: Selecting a Cell by Using VBA Range 

Here, we will select a cell containing the name Daniel Defoe. For this purpose, we will use the RANGE function in VBA.

Step-01:
➤Go to Developer Tab>>Visual Basic Option

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

After that, a Module will be created.

Step-02:
➤Write the following code

Sub SelectCell()
Range("B8").Select
End Sub

It will select cell B8.

➤Press F5

Result:
In this way, you will get the cell containing Daniel Defoe selected.

Read more: How to Use the Range Object of VBA in Excel

Method-2: Selecting a Group of Contiguous Cells by Using VBA Range

You can select a range of contiguous cells like the Student Name column and the Result column in the following table by following this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub ContiguousCells()
Range("B5:C10").Select
End Sub

It will select the cells from B5 to C10.

➤Press F5

Result:
After that, you will get the cells in Column B and Column C selected.

Method-3: Selecting a Group of Non-Contiguous Cells by Using VBA Range 

Suppose, you want to select the students named William David and Michael Anthony including their respective Email Id. To select these non-congruous cells you can follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub nonContiguous()
Range("B6,D6,B9,D9").Select
End Sub

It will select the cells B6, D6, B9, and D9.

➤Press F5

Result:
Then, you will get the cells containing the student’s name William David, Michael Anthony, and their respective Email Id selected.

Method-4: Selecting a Group of Non-Contiguous Cells and a Range by Using VBA Range 

You can select a range of cells and some non-contiguous cells simultaneously by following this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub nonContiguouswithrange()
Range("B5:B10,D6,D10").Select
End Sub

It will select the range of cells in the range B5:B10 and the other two cells D6, D10.

➤Press F5

Result:
Afterward, you will get the cells in the column Student Name and two Email Ids for William David and Donald Paul selected.

Method-5: Selecting a Range by Using VBA Range Offset

You can select a range of cells in the Student Name column by using the OFFSET function.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub selectrangeoffset()
Range("A1:A6").Offset(4, 1).Select
End Sub

At first, Range(“A1:A6”) will select the range A1:A6, and then Offset(4, 1) will move 4 rows downward from cell A1 and 1 column to the right side. After that, the equal number of cells in the range A1:A6 will be selected from here.

➤Press F5

Result:
In this way, you will select the column Student Name. 

Method-6: VBA Range Offset Negative

You can select the Email Id column by following this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub negativerangeoffset()
Range("F11:F16").Offset(-6, -2).Select
End Sub

At first, Range(“F11:F16”) will select the range F11:F16, and then Offset(-6, -2) will move 6 rows upward from cell F11 and 2 columns to the left side. After that, the equal number of cells in the range F11:F16 will be selected from here.

➤Press F5

Result:
After that, you will be able to select the column Email Id. 

Method-7: Selecting a Range with respect to Active Cell

Here, we have an active cell (cell A1) and with respect to this cell, we will select the data range in this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub actvcell()
Range(activecell.Offset(4, 1), activecell.Offset(9, 3)).Select
End Sub

Here, activecell is A1
The first part activecell.Offset(4, 1) will select a cell 4 rows downward and 1 column right from the cell A1 and the second part activecell.Offset(9, 3) will select a cell 9 rows downward and 3 columns right from cell A1.
Finally, all of the cells between these two cells will be selected.

➤Press F5

Result:
Then, you will be able to select the whole data range.

Method-8: Copy a Range 

If you want to copy a range of cells, then you can follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub copyrangeoffset()
Range("A1:A6").Offset(4, 1).Copy
End Sub

At first, Range(“A1:A6”) will select the range A1:A6, and then Offset(4, 1) will move 4 rows downward from cell A1 and 1 column to the right side. After that, the equal number of cells in the range A1:A6 will be selected from here.

Finally, it will copy the values in the range B5:B10.

➤Press F5
Result:
After that, you will be able to copy the data range in the Student Name column.

Method-9: Deleting a Range 

Here, we will show the way to delete a range of data by using VBA code.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrangeoffset()
Range("F11:F17").Offset(-7, -2).Delete
End Sub

Firstly, Range(“F11:F17”) will select the range F11:F17, and then Offset(-7, -2) will move 7 rows upward from cell F11 and 2 columns to the left side. After that, the equal number of cells in the range F11:F17 will be selected from here.

Finally, it will delete the range D4:D10.

➤Press F5

Result:
In this way, you will copy the data range in the Email Id column.

Method-10: Using VBA Range Offset to Enter a Value

Here, we have an empty cell ( we have removed the value in this cell for explaining this method) in the Student Name column and we want to fill it with the name Joseph Michael. By using a VBA code we can easily enter this value.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub valuerangeoffset()
Range("A1").Offset(6, 1).Value = "Joseph Michael"
End Sub

Firstly, Range(“A1”) will select the cell A1, and then Offset(6, 1) will move 6 rows downward from cell A1 and 1 column to the right side. After that, cell B7 will be selected and finally, it will enter the value “Joseph Michael” in this cell.

➤Press F5

Result:
In this way, you will get the name Joseph Michael in cell B7.

Method-11: Using VBA Range Offset to Get Output 

Suppose, you want to write Passed or Failed to correspond to the names of the students depending on the Result column where Pass or Fail has been written in a bracket. To find this substring in the Result column and write down it in the Pass/Fail column follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub CheckSubstring()
Dim cell As Range
    For Each cell In Range("C5:C10")
        If InStr(cell.value, "Pass") > 0 Then
            cell.Offset(0, 1).value = "Passed"
        Else
            cell.Offset(0, 1).value = "Failed"
        End If
    Next cell
End Sub

Here, the cell range C5:C10 is selected by Range(“C5:C10”) which is the Result column

InStr(cell. value, “Pass”) > 0 is the condition where the number is greater than zero (when the cell contains “Pass”)  then the following line will continue and give the output in the adjacent cell as Passed. Here, the adjacent cell will be selected by cell.Offset(0, 1), which means it will move 1 column right from the input cell.

If the condition becomes false means a cell doesn’t contain any “Pass” then the line under Else will execute and give the output value in the adjacent cell as Failed.

This loop will continue for each cell.

➤Press F5

Result:
Then, you will get the outputs Passed or Failed in the Pass/Fail column.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, I tried to cover the easiest ways to use VBA range offset in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Further Readings

• Excel VBA Copy Range to Another Sheet
• End of a Range Using VBA in Excel
• Excel Subscript Out of Range Error in VBA
• VBA for Each Cell in Range in Excel
• How to Count Text in Excel