We’re going to show you 3 methods in Excel VBA to select a range from active cells. To demonstrate this, we’ve picked a dataset with 3 columns: “First Name”, “Last Name”, and “Email”. We’ll select a cell and using Excel VBA we’ll select the range from that cell.
How to Select Range from Active Cell Using VBA in Excel: 3 Effective Ways
1. Employing VBA to Select Range from Active Cell to the Last Non-Blank Cell
For the first method, we’re going to use the Range.End property.
Here, we’ll input our code as a Module. To bring up the Module window, do these-
- Firstly, from the Developer tab >>> select Visual Basic.
The Visual Basic window will appear.
- Finally, from Insert >>> select Module.
This will bring up the Module window.
1.1. Using the End(xlUp) Property
In this section, we’re going to select the range upwards up to the last non-blank cell from our active cell.
Steps:
- Firstly, bring up the Module window.
- Secondly, type the following code.
Sub ToUp()
Range(ActiveCell, ActiveCell.End(xlUp)).Select
End SubWe’re calling our Sub Procedure ToUp. Then we’re selecting our range. The first value is our active cell. The last value is ActiveCell.End(xlUp). Finally, we’re selecting the range with the Range.Select method.
- Thirdly, Save it and close the window.
- After that, select cell C6. This cell is our active cell.
Now, we’re going to bring up the Macro window. To do that-
- From the Developer tab >>> select Macros.
After that, the Macro dialog box will appear.
- Then, select “ToUp” from the “Macro name:”.
- Finally, click on Run.
We can see that, we’ve selected the cell range C4:C6.
1.2. Incorporating the End(xlDown) Property
In this section, we’re going to select the range downwards from our active cell.
Steps:
- Firstly, bring up the Module window.
- Secondly, type the following code.
Sub ToDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End SubWe’re calling our Sub Procedure ToDown. Then we’re selecting our range. The first value is our active cell. The last value is ActiveCell.End(xlDown). Finally, we’re selecting the range with the Range.Select method.
- Thirdly, Save it and close the window.
- After that, select cell C6. This is our active cell.
- Then, bring up the Macro dialog box.
- Select “ToDown”.
- Finally, click on Run.
Thus, we’ve selected a range from our active cell using Excel VBA.
Read More: Excel VBA to Select Used Range in Column
1.3. Applying the End(xlToLeft) Property
In this section, we’re going to select the range to the left of our active cell.
Steps:
- Firstly, bring up the Module window.
- Secondly, type the following code.
Sub ToLeft()
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End SubWe’re calling our Sub Procedure ToLeft. Then we’re selecting our range. The first value is our active cell. The last value is ActiveCell.End(xlToLeft). Finally, we’re selecting the range with the Range.Select method.
- Thirdly, Save it and close the window.
- After that, select cell D7. This is our active cell.
- Then, bring up the Macro dialog box.
- Select “ToLeft”.
- Finally, click on Run.
Thus, we’ve selected a range from our active cell using Excel VBA.
1.4. Implementing the End(xlToRight) Property
In this section, we’re going to select the range to the right of our active cell.
Steps:
- Firstly, bring up the Module window.
- Secondly, type the following code.
Sub ToLeft()
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End SubWe’re calling our Sub Procedure ToRight. Then we’re selecting our range. The first value is our active cell. The last value is ActiveCell.End(xlToRight). Finally, we’re selecting the range with the Range.Select method.
- Thirdly, Save it and close the window.
- After that, select cell C8. This is our active cell.
- Then, bring up the Macro dialog box.
- Select “ToRight”.
- Finally, click on Run.
Thus, we’ve selected a range from our active cell using Excel VBA.
2. Using the VBA Range.Offset Property to Select Range from Active Cell Using VBA in Excel
For the second method, we’re going to use the VBA Range.Offset property to select the range using our active cell.
Steps:
- Firstly, type the following code into the Module window.
Sub UsingOffset()
Range(ActiveCell, ActiveCell.Offset(1, 2)).Select
End SubWe’ve created our Sub Procedure UsingOffset. Then we’re selecting our range. The first value is our active cell. The last value is ActiveCell.Offset(1,2). With the Offset property we’re moving 1 row down and 2 columns right. Finally, we’re selecting the range with the Range.Select method.
- Secondly, Save it and close the window.
- After that, select cell B8. This is our active cell.
- Then, bring up the Macro dialog box.
- Select “UsingOffset”.
- Finally, click on Run.
In conclusion, we’ve selected a range from the active cell. Moreover, this is what the final step should look like.
Read More: Excel VBA: Select Range with Offset Based on Active Cell
3. Select Range from Active Cell Using VBA in Excel by Utilizing the CurrentRegion Property
For the last method, we’ll be using the Range.CurrentRegion property.
Steps:
- Firstly, type the following code into the Module window.
Sub cRegion()
ActiveCell.CurrentRegion.Select
End SubWe’re calling our Sub Procedure cRegion. Then we’re selecting our range. With the CurrentRegion property, we’re selecting the range up to a blank cell. Finally, we’re selecting the range with the Range.Select method.
- Secondly, Save it and go back to the Excel sheet.
- After that, select cell C10. This is our active cell.
- Then, bring up the Macro dialog box.
- Select “cRegion”.
- Finally, click on Run.
Thus, we’ve selected the range up to a blank cell.
Practice Section
We’ve provided practice datasets for each method in the Excel file.
Download Practice Workbook
Conclusion
We’ve shown you 3 methods of Excel VBA select range from active cell. If you face any problems understanding the steps, feel free to comment below. Thanks for reading, keep excelling!
