How to Use VBA to Select Range from Active Cell in Excel (3 Methods)

We’re going to show you 3 methods in Excel VBA to select range from active cell. 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.

excel vba select range from active cell


Download Practice Workbook


3 Ways to Select Range from Active Cell Using VBA in Excel

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-

Steps:

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

excel vba select range from active cell


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:

Sub ToUp()
Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub

We’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.

excel vba select range from 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.

excel vba select range from active cell

We can see that, we’ve selected the cell range C4:C6.

Read More: Excel VBA: Copy Dynamic Range to Another Workbook


1.2. Incorporating the End(xlDown) Property

In this section, we’re going to select the range downwards from our active cell.

Steps:

Sub ToDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

We’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.

excel vba select range from active cell

  • Thirdly, Save it and close the window.
  • After that, select cell C6. This is our active cell.

excel vba select range from active cell

Thus, we’ve selected a range from our active cell using Excel VBA.

Read More: Excel VBA to Loop through Range until Empty Cell (4 Examples)


Similar Articles


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:

Sub ToLeft()
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End Sub

We’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.

excel vba select range from active cell

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

excel vba select range from active cell

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:

Sub ToLeft()
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub

We’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.

excel vba select range from active cell

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

excel vba select range from active cell

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:

Sub UsingOffset()
Range(ActiveCell, ActiveCell.Offset(1, 2)).Select
End Sub

We’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.

excel vba select range from active cell

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

excel vba select range from active cell

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: Get Range of Cells with Values (7 Examples)


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:

Sub cRegion()
ActiveCell.CurrentRegion.Select
End Sub

We’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.

excel vba select range from active cell

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

excel vba select range from active cell

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.

excel vba select range from active cell


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!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo