How to Use VBA to Select Range from Active Cell in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.


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 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:

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: 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:

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


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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo