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:


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.