Excel VBA: Select Range with Offset Based on Active Cell

Looking for ways to select a range with offset based on the active cell using VBA in Excel? Then, this is the right place for you.

In order to select a range of cells based on active cell, we can surely use the Offset function in VBA. I have tried to show the overview in the above video. For more details, go through the below section.

Download Practice Workbook

You can download the practice workbook from here.

How to Launch VBA Editor in Excel

We often create a module in order to work with VBA. First of all, we need to go to the Developer tab and select Visual Basic to create a module.

Once you have opened the editor, then click on the Insert tab and pick the Module option.

A module will be created and we can write the required code in that module to execute the program.

Introduction of Selecting Range with Offset Property in Excel VBA

In order to navigate from one cell to another in a sheet, we can use the Offset property in VBA. We can also select a range using Offset. Here, we just need to define the rows and columns to navigate the range selection.

Consider a case where you want to select a cell two rows down and three columns right of the current cell. Just use the following command in your code:

ActiveCell.Offset(2, 3).Select

This is how simple and easy Offset is to select a range.

Excel VBA: Select Range with Offset Based on Active Cell (2 Examples)

We can select a range with the help of Offset along with For Loop, If, and MsgBox. I have tried to explain the range selection in detail in the below part. Go through it to have a clear idea.

1. Range Selection with Offset Based on Active Cell

In the above video, I have tried to show the calculation of finding the summation of a few months’ sales for every employee. My aim is to do the range selection keeping the cursor in B5 as an active cell and doing the task. I have used the following VBA code with Offset for this purpose.

Sub Range_Selection()
'Use For Loop in Active Cell to loop through all non-blank cells on that column
For j = 1 To 10
sums = 0
'Activate new cell using Offset
Set new_active = ActiveCell.Offset(j - 1, 0)
'Use For Loop to have summation
For i = 2 To 4
sums = sums + new_active.Offset(0, i).Value
Next i
'Have summation on a preferred cell
new_active.Offset(0, 5).Value = sums
Next j
End Sub

2. Select a Range Using Offset with Active Cell

We can use Offset to select a range based on active cells quite easily. With the Offset function here, I have selected all the cells that contain sales value only from D5 to F14 and shown the highest sales in a MsgBox. I have shown a demo in the above video. To execute the purpose, I have used the following VBA code.

Sub Selecting_Range_with_Offset()
'Range defining with Offset
Set Rn = Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(9, 4))
Highest_Sales = Rn.Cells(1).Value
'Use For Loop to have highest sales amount
For j = 1 To Rn.Cells.Count
    If Rn.Cells(j + 1) > Highest_Sales Then
        Highest_Sales = Rn.Cells(j + 1).Value
    Else
        Highest_Sales = Highest_Sales
    End If
Next j
MsgBox "The amount of highest sale is " & Highest_Sales
End Sub

Alternative: Using Cells

We can do the same thing without Offset too. I have used Offset previously to select a range. I can do the same thing with the Selection object. With the help of Row and Column properties in the Selection object, we can do it with ease.

Sub Selecting_Range_with_ActiveCell()
'Range defining without Offset
Set Rn = Range(Cells(Selection.Row, Selection.Column + 2), Cells(Selection.Row + 9, Selection.Column + 4))
Highest_Sales = Rn.Cells(1).Value
For j = 1 To Rn.Cells.Count
    If Rn.Cells(j + 1) > Highest_Sales Then
        Highest_Sales = Rn.Cells(j + 1).Value
    Else
        Highest_Sales = Highest_Sales
    End If
Next j
MsgBox "The amount of highest sale is " & Highest_Sales
End Sub

How to Use Offset Property to Copy Range in Excel VBA

Offset can also be used to Copy a range of cells based on selection. In the above video, I have tried to show that. I have set criteria to pass the exam and applied them in the top five cells in column E. With Offset, I have applied the same criteria for the later part of the column.

Sub Copy_Range_with_Offset()
'Copy Range with offset
Range(ActiveCell.Offset(0, 3), ActiveCell.Offset(4, 3)).Copy
'Paste the copied value with Offset
Range(ActiveCell.Offset(5, 3), ActiveCell.Offset(9, 3)).PasteSpecial
'Diminish the selection of copied area
Application.CutCopyMode = False
End Sub

Dealing with Different Active.Offset() Property

1. What Does ActiveCell.Offset(0, -2) Mean?

In VBA, the property ActiveCell.Offset(0, -2) produces a range object that represents a cell that is two columns to the left of the presently active cell.

0 represents the row value and -2 represents the column value. Offset considers the row and column values and shifts the cursor according to them. In the above video, I have shown the value of the cell shifted two columns in the same row in a MsgBox using ActiveCell.Offset(0, -2).

Sub Meaning_Explanation()
'Select cell with ActiveCell.Offset(0, -2)
ActiveCell.Offset(0, -2).Select
MsgBox "This result belongs to " & ActiveCell.Value
End Sub

2. Getting Total of a Range with ActiveCell.Offset Property

Offset is used vastly to select multiple cells of range. It is highly recommended to do so. Offset(0,1) property can be used as a starting point to select multiple cells and create a range. I have shown an overview of it in the above video. My aim is to have the summation of a range based on the active cell and have the output in a cell set with Offset. The code is given below.

Sub Select_Multiple_Cells()
'Select multiple cells with Offset(0,1)
Set Rng = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(9, 1))
'Find the summation of the selected cells
Total = Application.WorksheetFunction.Sum(Rng)
'Place the result in a preferred location
ActiveCell.Offset(10, 1) = Total
End Sub

Excel VBA: Offset to Specific Column

You can keep your cursor anywhere in the worksheet and it will give the value of the same row but a fixed column, is it possible?
You got it. It is certainly possible by offsetting the rest of the cells. The VBA code is written below.

Sub Specific_Cells_Selection()
'Column "B" value return based on row selection
Employee_Name = Cells(ActiveCell.Row, "B").Value
MsgBox "This row represents " & Employee_Name
End Sub

Select Cell Based on Active Cell Without Offset in Excel VBA

In the above sections, I have mostly shown range selection with Offset. I can select a certain cell without Offset depending on the active cell. The VBA code for this is given below.

Sub Active_a_Cell_Without_Offset()
'Select cell without Offset based on active cell
Desired_Cell_Selection = Cells(ActiveCell.Row, ActiveCell.Column + 4).Select
MsgBox "The bonus amount is " & ActiveCell.Value
End Sub

Here, I have kept my cursor in column B to define an active cell and used ActiveCell.Column + 4 to show the Bonus Amount of that person in a MsgBox.

Conclusion

If you have gone through the above sections, I think you have got a clear vision of range selection with offset. I have tried to explain all the possible ways of range selections with Offset based on the active cell and other ways too. It will be a matter of great pleasure for me if my article helps you even a little. For more Excel-related articles, you can visit our site exceldemy.com.