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.

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

To 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 Sub

Note 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 Sub

And if you want to select the entire range of data in a column:

    Sheet1.Range("C2", ActiveSheet.Range("C2").End(xlDown)).Select

Sample 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")).Select

Sample 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 Sub

 

Sample 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 Sub

 

Needless 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 Sub

 

Sample 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 Sub

Sample 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 Sub

 

Sample 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 Sub

Here 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 Sub

Sample 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 Sub

 

Note 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 Sub

For this we use the resize method. After execution of the first step, the selection looks like

And after resizing:



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.


Leave a Reply

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



© Copyright 2018 software-solutions-online.com.