How to Use VBA Range Offset (11 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.

Download Workbook

11 Ways to Use VBA Range Offset

I have the following data table containing some students’ information of 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 versions according to your convenience.

VBA range offset

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.

VBA range offset

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

selecting a cell

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

VBA range offset

After that, a Module will be created.

selecting a cell

Step-02:
➤Write the following code

Sub SelectCell()

Range("B8").Select

End Sub

It will select cell B8.

selecting a cell

➤Press F5

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

VBA range offset

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.

VBA range offset

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.

selecting a group of contiguous cells

➤Press F5

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

selecting a group of contiguous cells

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.

VBA range offset

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.

selecting non-contiguous cells

➤Press F5

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

selecting non-contiguous cells

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.

VBA range offset

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.

selecting non-contiguous cells and a range

➤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.

selecting non-contiguous cells and a range

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.

VBA range offset

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.

using range and offset

➤Press F5

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

using range and offset

Method-6: VBA Range Offset Negative

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

VBA range offset

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.

using range and negative offset

➤Press F5

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

using range and negative offset


Similar Readings:


Method-7: Selecting a Range with respect to the 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.

VBA range offset

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.

selecting a range with respect to active cell

➤Press F5

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

selecting a range with respect to active cell

Method-8: Copy a Range 

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

VBA range offset

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.

copy a range

 

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

copy a range

Method-9: Deleting a Range 

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

VBA range offset

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.

delete a range

➤Press F5

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

delete a range

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.

VBA range offset

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.

enter a value

➤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.

VBA range offset

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.

output

➤Press F5

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

output

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.

practice

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

Tags:

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo