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
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
Sub RangeSelect() Sheets("AnotherWorksheet").Activate ActiveSheet.Range(Cells(2, 2), Cells(9, 6)).Select End Sub
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