Using the VBA Range.Select Method to Select a Range of Cells
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.
Contents
Sample code # 1: Selecting a range of cells on the active worksheet
Sub RangeSelect() ActiveSheet.Range("B2:E5").Select End SubOR
Sub RangeSelect() ActiveSheet.Range("B2", "E5").Select End SubSample 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 SubOR
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 rangeThis is commonly used when selecting a range of data, skipping the header row.
Sub RangeSelect() ActiveSheet.Range("B1:E5").Offset(2, 0).Select End SubTo include rows above or columns to the left of the specified range, use negative integers for offset
Sub RangeSelect() ActiveSheet.Range("C3:F8").Offset(-1, -1).Select End SubNote that the offset works on the entire range, i.e. for a offset of -1 rows on range C3:F8 will result in C2:F7 and an offset of -1 columns will eventually result in the range B2:E7
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 SubAnd if you want to select the entire range of data in a column:
Sheet1.Range("C2", ActiveSheet.Range("C2").End(xlDown)).SelectSample code # 5: Selection using named ranges
You can also use named ranges, instead of specifying the actual range. For instance,
Application.Union(Range("RangeA"), Range("RangeB")).SelectSample code # 6: Selecting non-contiguous data
You can also select non-contiguous data, though you may not find much practical use for it
Sub nonContiguous() Sheet1.Range("B2:E2,D2:D8").Select End SubSample code # 7: Selecting non-contiguous data using union
This is similar to the above method, by using a different approach
Sub nonContiguous() Dim y As Range Set y = Application.Union(Range("Sheet1!B2:C3"), Range("Sheet1!C3:D4")) y.Select End SubNeedless to say, when working with multiple ranges, all the ranges must lie on the same sheet, or you will get an error. For instance, this piece of code will not work as the two ranges are on 2 different sheets. Or if you’re using named ranges, “RangeA” and “RangeB” should be on the same sheet.
Sub nonContiguous() Dim y As Range 'This statement will throw an error -- Union method of application class failed Set y = Application.Union(Range("Sheet1!B2:C3"), Range("Sheet2!C3:D4")) y.Select End SubSample code # 7: Selecting intersection of two ranges
Just like union, you can use intersect to select the intersection of two ranges.
Sub selectIntersect() Dim y As Range Set y = Application.Intersect(Range("Sheet1!B2:C4"), Range("Sheet1!C3:D4")) y.Select End SubSample code # 8: Selecting a rectangular range around specified cell
This can prove useful when you have multiple tables in a sheet and you want to do some operation, like say plot a graph, on any one region of the sheet. For this you use the CurrentRegion Method. CurrentRegion is an area bounded by any combination of blank rows and blank columns.
Sub selectCurReg() ActiveSheet.Range("D3").CurrentRegion.Select End SubSample code # 9: Selecting a rectangular range around specified cell including blanks
The above code (# 8) works fine when there are no blank rows or columns in your data. If you need to include data beyond the blanks, use the code below
Sub selectCurReg() Dim lastCol As Long, lastRow As Long lastCol = ActiveSheet.Range("B2").End(xlToRight).Column lastRow = ActiveSheet.Cells(65536, 2).End(xlUp).Row ActiveSheet.Range("B2", ActiveSheet.Cells(lastRow, lastCol)).Select End SubHere we are simply getting the last non empty row and column and selecting everything in between
Sample code # 10: Selecting an entire row
Use this code to select the entire second row
Sub selectRow() Range("2:2").Select End SubSample code # 11: Selecting a range in a sheet from another workbook
So far, we have seen only how to select a cell / range from within a workbook. It is also possible to select a range from another workbook through VBA:
Sub selectFromAnotherWB() Application.Goto Workbooks("Book2").Sheets("Sheet1").Range("A1:E10") End SubNote that this will work only if both the Excel files are saved in the same location. If the file paths are different for both, use the complete path to the file
All of the examples in the article are well applicable to worksheets from another workbook as well.
Sample code # 12: Selecting a range and then resize the selection
Sub selectFromAnotherWB() Range("B2:F6").Select Selection.Resize(Selection.Rows.Count + 1, Selection.Columns.Count - 1).Select End SubFor this we use the resize method. After execution of the first step, the selection looks like
And after resizing:
You can find more information on counting rows in Excel here. And more info about dynamically selecting ranges with unknown size, here.These were some of the most common ways to select a cell or range of cells using VBA. It is worth mentioning here that, it is best to avoid using select and selection whenever you can. This will prevent Run-time errors that can occur if a user happens to select another cell while the code is being executed. In truth, there should be very few cases you will actually need to use these methods.
One thought on “Using the VBA Range.Select Method to Select a Range of Cells”