Table of Contents
Selecting a range from the active cell in VBA is a simple process. To do so, you first need to define the active cell using the ActiveCell property of the Range object, and then use the Range.Select method to select the desired range from the active cell. The range selection is based on the relative position of the active cell to the range that you want to select. For example, if the active cell is A1, and you want to select range A3:B5, you can use the Range(“A1:A3, B1:B5”).Select command. The Range object also provides a number of other methods that can be used to select certain ranges with more precision.
You can use the following methods in VBA to select a range of cells in Excel starting from the currently active cell:
Method 1: Select Range Down from Active Cell
Sub SelectActiveDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
This macro will select the range from the active cell down to the last used cell in the column.
Method 2: Select Range Up from Active Cell
Sub SelectActiveUp()
Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub
This macro will select the range from the active cell up to the first used cell in the column.
Method 3: Select Range to Right from Active Cell
Sub SelectActiveRight()
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub
This macro will select the range from the active cell to the last used cell to the right in the same row.
Method 4: Select Range to Left from Active Cell
Sub SelectActiveLeft()
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End Sub
This macro will select the range from the active cell to the last used cell to the left in the same row.
The following examples show how to use each method with the following sheet in Excel:
Example 1: Select Range Down from Active Cell
Suppose we currently have cell C3 selected.
Sub SelectActiveDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
When we run this macro, the following range is automatically selected:
Notice that the range from cell C3 down to the last used cell in the column is now selected.
Example 2: Select Range Up from Active Cell
Suppose we currently have cell C3 selected.
We can create the following macro to select the range from the active cell up to the first used cell in the column:
Sub SelectActiveUp()
Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub
When we run this macro, the following range is automatically selected:
Notice that the range from cell C3 up to the first used cell in the column is now selected.
Example 3: Select Range to Right from Active Cell
Suppose we currently have cell B2 selected.
We can create the following macro to select the range from the active cell to the last used cell to the right in the same row:
Sub SelectActiveRight()
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub
When we run this macro, the following range is automatically selected:
Notice that the range from cell B2 to the last used cell to the right in the same row is now selected.
Example 4: Select Range to Left from Active Cell
Suppose we currently have cell D6 selected.
We can create the following macro to select the range from the active cell to the last used cell to the left in the same row:
Sub SelectActiveLeft()
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End Sub
When we run this macro, the following range is automatically selected:
Notice that the range from cell D6 to the last used cell to the left in the same row is now selected.