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:
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
First of all, I have defined a sub_procedure named Range_Selection. With the help of the first For Loop, I have gone through each non-blank cell under the active cell. Next, I have used the Offset property under the ActiveCell object to navigate the sales selection. Another For Loop is induced here to have the summation of sales. Finally, I have set a suitable location with Offset to have the summation value.
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
Under the Selecting_Range_with_Offset sub_procedure, I have selected the entire range using Offset based on the active cell and set it as Rn. Then, considered the first cell value in the range as Highest_Sales. After that, I have gone through each cell within Rn and compared them among themselves to find the highest value in a MsgBox.
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
In Selecting_Range_with_Offset sub_procedure, I have selected a range of cells with Offset keeping the cursor in cell B5. Then, I copied the cells and paste them into the rest of the cells of that column. I have used Application.CutCopyMode to diminish the selection of copied area.
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
ActiveCell.Offset(0, 1) is used as a starting point of a range in Select_Multiple_Cells sub_procedure. Then, I have calculated the total value with Application.WorksheetFunction.Sum and placed it in a cell titled Total.
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
In Specific_Cells_Selection sub_procedure, I have defined the row from active cell and column B value which will show output in a MsgBox.
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.
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.