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.
We can create the following macro to select the range from the active cell down to the last used cell in the column:
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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Find Last Used Row
VBA: How to Find Last Used Column
VBA: How to Insert Multiple Rows
This example is clear to me:
Range(ActiveCell, ActiveCell.End(xlDown)).Select
but now I want to enlarge this selection with one column to the right.
Hi Bert…To enlarge the selection to include one column to the right, you can modify your VBA code as follows:
“`vba
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 1).Select
“`
### Explanation:
1. **`Range(ActiveCell, ActiveCell.End(xlDown)).Select`:**
– This selects a range starting from the `ActiveCell` and extending down to the last cell in the column before encountering an empty cell.
2. **`Selection.Resize`:**
– The `Resize` method changes the size of the current selection.
– `Selection.Rows.Count` keeps the number of rows in the selection unchanged.
– `Selection.Columns.Count + 1` adds one additional column to the selection.
3. **`Select`:**
– The `.Select` method applies the enlarged range as the active selection.
Now, your selection will include the original range and extend one column to the right.
What if I wanted to select from the ActiveCell 10 rows down & 6 columns to delete cells & move all cells up irrespective of whether cells are empty or not?
Hi Marc…To select a range starting from the active cell, extend it 10 rows down and 6 columns across, and then delete those cells while moving the cells up, you can use the following VBA code:
### **VBA Code**
“`vba
Sub DeleteRangeMoveUp()
Dim targetRange As Range
‘ Define the range starting from the ActiveCell
Set targetRange = ActiveCell.Resize(10, 6)
‘ Delete the range and move cells up
targetRange.Delete Shift:=xlUp
End Sub
“`
—
### **Explanation**
1. **`ActiveCell.Resize(10, 6)`**:
This adjusts the size of the range starting from the `ActiveCell` to include 10 rows and 6 columns.
2. **`.Delete Shift:=xlUp`**:
Deletes the selected range and shifts the remaining cells up to fill the space.
—
### **Steps to Use the Code**
1. Open the **VBA Editor**:
– Press `Alt + F11`.
2. Insert a **New Module**:
– Go to `Insert` > `Module`.
3. Copy and paste the code above into the module.
4. Close the VBA Editor and return to Excel.
5. Run the Macro:
– Press `Alt + F8`, select `DeleteRangeMoveUp`, and click `Run`.
—
### **Important Notes**
– Ensure the active cell is appropriately positioned before running the macro.
– Adjust the `Resize` parameters (`10, 6`) to select a different size of the range.
– This operation will affect your worksheet directly, so it’s a good idea to back up your data before running the macro.
Hi James, as I emailed you after asking the question, I’d worked it out myself & tested it with dummy data using step into. Your explanation notes are brilliant & thank you for taking time out for them.
I’m busy building a database for an app I’ll be trying to release so, I’m sure to be back?
Thank you once again.
Sounds great Marc! Keep us posted on your progress!