How to Maximize Your Use of VBA Range.Select Method

The Range.Select method is used by VBA Macro developers for various purposes. These can be as simple as selecting a cell on an active worksheet, selecting one on another worksheet, or selecting a range of cells on a worksheet from a different workbook.

Sample code # 1: Selecting a range of cells on the Active Worksheet

Sub RangeSelect()
ActiveSheet.Range("B2:E5").Select
End Sub

OR

Sub RangeSelect()
ActiveSheet.Range("B2", "E5").Select
End Sub

Sample code # 2: Selecting a range of cells on another Worksheet in the same Workbook

Notice that the sample code below doesn’t require us to have “select” text. This is another way of selecting a range of cells by using “Application.Goto” which basically has the same result as “Range.select” method. However, you can still use the “select” method by activating the target worksheet before selecting the specified range.

Sub RangeSelect()
Application.Goto ActiveWorkbook.Sheets("AnotherWorksheet").Range("B2:F9")
End Sub

OR

Sub RangeSelect()
Sheets("AnotherWorksheet").Activate
ActiveSheet.Range(Cells(2, 2), Cells(9, 6)).Select
End Sub


Sample code # 3: Selecting a range of cells offset from a specified range

This is commonly used when selecting a range of data, skipping the header row.

Sub RangeSelect()
ActiveSheet.Range("B1:E5").Offset(2, 0).Select
End Sub

Sample code # 4: Selecting the last cell of a column of contiguous Data

The following sample code is used to select the last row of the specified column (ex. column “B”) that has data or is a non-blank cell.

Sub RangeSelect()
ActiveSheet.Range("B2").End(xlDown).Select
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *