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.
Download Workbook
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.
Similar Readings:
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.
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.