How To Use VBA To Select Range From Active Cell In Excel (3 Methods)

Method 1 – Employing VBA to Select Range from Active Cell to the Last Non-Blank Cell

Steps:

• From the Developer tab >>> select Visual Basic.

The Visual Basic window will appear.

• From Insert >>> select Module.

This will bring up the Module window.

1.1. Using the End(xlUp) Property

Steps:

• Bring up the Module window.
• Type the following code.

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). We’re selecting the range with the Range.Select method.

• Save it and close the window.
• Select cell C6. This cell is our active cell.

Bring up the Macro window. To do that-

• From the Developer tab >>> select Macros.

The Macro dialog box will appear.

• Select “ToUp” from the “Macro name:”.
• Click on Run.

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

1.2. Incorporating the End(xlDown) Property

Steps:

• Bring up the Module window.
• Type the following code.

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

We’re calling our Sub Procedure ToDown and selecting our range. The first value is our active cell. The last value is ActiveCell.End(xlDown). We’re selecting the range with the Range.Select method.

• Save it and close the window.
• Select cell C6. This is our active cell.

• Bring up the Macro dialog box.
• Select “ToDown”.
• Click Run.

We selected a range from our active cell using Excel VBA.

1.3. Applying the End(xlToLeft) Property

Steps:

• Bring up the Module window.
• Type the following code.

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). We’re selecting the range with the Range.Select method.

• Save it and close the window.
• Select cell D7. This is our active cell.

• Bring up the Macro dialog box.
• Select “ToLeft”.
• Click Run.

We selected a range from our active cell using Excel VBA.

1.4. Implementing the End(xlToRight) Property

Steps:

• Bring up the Module window.
• Type the following code.

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). We’re selecting the range with the Range.Select method.

• Save it and close the window.
• Select cell C8. This is our active cell.

• Bring up the Macro dialog box.
• Select “ToRight”.
• Click Run.

We selected a range from our active cell using Excel VBA.

Method 2 – Using the VBA Range.Offset Property to Select Range from Active Cell Using VBA in Excel

Steps:

• Type the following code into the Module window.

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. We’re selecting the range with the Range.Select method.

• Save it and close the window.
• Select cell B8. This is our active cell.

• Bring up the Macro dialog box.
• Select “UsingOffset”.
• Click Run.

We selected a range from the active cell. This is what the final step should look like.

Method 3 – Select Range from Active Cell Using VBA in Excel by Utilizing the CurrentRegion Property

Steps:

• Type the following code into the Module window.

Sub cRegion() ActiveCell.CurrentRegion.Select End Sub

We’re calling our Sub Procedure cRegion. Then we select our range. With the CurrentRegion property, we select the range up to a blank cell. We’re selecting the range with the Range.Select method.

• Save it and go back to the Excel sheet.
• Select cell C10. This is our active cell.

• Bring up the Macro dialog box.
• Select “cRegion”.
• Click Run.

We selected the range up to a blank cell.

Download Practice Workbook

Select Range from Active Cell.xlsm

Related Articles

• How to Select Range Based on Cell Value VBA
• Excel VBA to Cancel Selection

Get FREE Advanced Excel Exercises with Solutions!