How To Use VBA To Select Range From Active Cell In Excel (3 Methods)
Có thể bạn quan tâm
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.
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.
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.
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.
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.
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.
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.xlsmRelated Articles
- How to Select Range Based on Cell Value VBA
- Excel VBA to Cancel Selection
Từ khóa » Visual Basic Excel Range Select
-
How To Select Cells Or Ranges By Using Visual Basic Procedures In ...
-
Range Object (Excel) | Microsoft Docs
-
VBA Select Range / Cells - Automate Excel
-
Working With Cells And Ranges In Excel VBA (Select, Copy, Move ...
-
How To Select A Range/Cell Using VBA In Excel
-
How To SELECT ALL The Cells In A Worksheet Using A VBA Code
-
Range Object In Excel VBA - Easy Dim And Set
-
Select A Range In Excel VBA - ExcelHowto
-
Using For Each In Excel Range - Code VBA
-
Đối Tượng Range Trong Excel VBA - Viblo
-
Đối Tượng Range Trong Excel VBA - VietTuts
-
Excel VBA Range Object: 18 Useful Ways Of Referring To Cell Ranges
-
3 Ways To Select Cells And Ranges In Excel Visual Basic - WikiHow
-
Excel VBA Range Object - Guru99