In Excel VBA, you don’t have to explicitly select cell ranges before modifying them. You can directly manipulate cell ranges without explicitly selecting them, which enhances your code’s performance and readability.
However, selecting cell ranges becomes necessary if you want the code to guide the user to a new location in the current workbook or another workbook after the code has run.
In this tutorial, I will show you examples of selecting a single cell, a static range, and a dynamic range in Excel using the ‘Range.Select’ method in VBA. The ‘Range.Select’ method allows you to choose specific cells or cell ranges.
VBA to Select Single Cell
Let’s first look at some examples of how to select single cells using VBA.
Example #1: VBA to Select a Single Cell on the Active Sheet
Suppose you have the following dataset on the active sheet:
You can use the VBA code below to select cell A2:
Sub SelectOneCellOnActiveSheet()
Range("A2").Select
End Sub
When you run the code, it selects cell A2 on the active sheet:
Note: You can also use the ‘Range.Activate’ method to select a single cell as in the code below:
Sub ActivateCellActiveSheet()
Range("A2").Activate
End Sub
Example #2: Use the ‘Range.Offset’ Property to Select a Cell or Cell Range
You can utilize the ‘Range.Offset’ property to select a cell or cell range based on the location of the active cell. The property is helpful when you don’t need to know the specific address of the cell or cell range you want to select.
The syntax of the property:
Range.Offset(RowOffset, ColumnOffset)
The ‘RowOffset’ and ‘ColumnOffset’ parameters can be positive or negative integers or zero. Positive integers make the property move down and then to the right.
Negative integers tell the property to move up and then to the left. The zero integer indicates that the code does not offset the active cell’s row or column.
Assume you have the dataset below on the active sheet:
Notice that cell A3 is the active cell.
You can use the VBA code below to select the cell containing the GDP of Brazil, the name of the country in the active cell:
Sub SelectCellOffset()
Range("A3").Offset(0, 2).Select
End Sub
When you run the subroutine, it selects cell C3, which is zero rows down and two columns to the right relative to the active cell:
You can also select a cell range using the ‘Range.Offset’ property.
Suppose you have the dataset below on the active worksheet:
You can use the following code to select the cell range that is three rows below and two columns to the right of the cell range A2:A4:
Sub SelectRangeOffset()
Range("A2:A4").Offset(3, 2).Select
End Sub
When you execute the code, it selects the cell range C5:C7 depicted below:
Also read: Excel VBA Clear Range
Example #3: VBA to Select a Single Cell on a Specific Sheet
Suppose you have the following dataset on Sheet2 in the current workbook:
You can use the VBA code below to select cell C7:
Sub SelectCellSpecificSheet()
Worksheets("Sheet2").Activate
Range("C7").Select
End Sub
When you run the code, it selects cell C7 on Sheet2:
The VBA code activates Sheet2 and selects cell C7. If you don’t activate the worksheet before selecting, you get a ‘runtime error 1004: Select method of Range class failed.’
If the dataset was on, say, Sheet2 of another open workbook named ‘Economy,’ you would use the following code to select cell C7:
Sub SelectCellAnotherWorkbook()
Workbooks("Economy.xlsx").Worksheets("Sheet2").Activate
Range("C7").Select
End Sub
Also read: VBA to Find Last Row in Excel
VBA to Select a Fixed/Static Range
Now, let’s look at some examples of how to select a fixed range of cells using VBA.
Example #1: Use Cell References to Select a Fixed Range
If you know the dimensions of a range, you can use the cell reference of the range in your code to select the range.
Suppose you have the following dataset on the active sheet:
You can select the cell range A2:A7using the VBA code below:
Sub SelectRangeFixedSize()
Range("A1:C7").Select
End Sub
When You execute the code, it selects the range A1:C7, as shown below:
Note: You can also use the version of the code below to achieve the same results:
Sub SelectRangeFixedSize2()
Range("A1", "C7").Select
End Sub
This version of the code uses the top-left and bottom-right cells of the range to determine the size of the range and then selects it.
Note: You can customize and use the code below to select a fixed size range on a specific sheet in the same workbook:
Sub SelectRangeFixedSizeSpecificSheet()
Worksheets("Sheet2").Activate
Range("A1:C7").Select
End Sub
To select a fixed size range on a worksheet in another open workbook, customize and use the code below:
Sub SelectRangeFixedSizeAnotherWorkbook()
Workbooks("Economy.xlsx").Worksheets("Sheet3").Activate
Range("A1:C7").Select
End Sub
Example #2: Select a Static Named Range
Suppose you have the static range below named ‘Economy’ on Sheet4 of the current workbook:
You can use the VBA code below to select the static named range:
Sub SelectStaticNamedRange()
Dim ws As Worksheet
Dim staticRange As Range
Set ws = ThisWorkbook.Sheets("Sheet3")
Set staticRange = ws.Range("Economy")
ws.Activate
staticRange.Select
End Sub
When you execute the code, it activates Sheet3 and selects the ‘Economy’ named range.
You can customize and use the below VBA code to select a static named range in another open workbook:
Sub SelectNamedRangeInAnotherWorkbook()
Dim otherWorkbook As Workbook
Dim ws As Worksheet
Dim staticRange As Range
Set otherWorkbook = Workbooks("Inventory.xlsx")
Set ws = otherWorkbook.Sheets("Sheet3")
Set staticRange = ws.Range("Economy")
otherWorkbook.Activate
ws.Select
staticRange.Select
End Sub
Also read: VBA to Copy Range to Email Body
VBA to Select a Dynamic Range
Sometimes, the range you want to select may be dynamic because it automatically expands or contracts as data is added or removed. You can select such dynamic ranges using the various techniques explained below.
Example #1: Select a Dynamic Named Range
Suppose you have the following dynamic dataset named ‘Country_GDP’ on Sheet4:
You can select the dynamic named range using the VBA code below:
Sub SelecDynamicNamedRange()
Dim ws As Worksheet
Dim dynamicRange As Range
Set ws = ThisWorkbook.Sheets("Sheet4")
Set dynamicRange = ws.Range("Country_GDP")
ws.Activate
dynamicRange.Select
End Sub
When you execute the subroutine, it selects the dataset, as shown below:
If we remove, say, four countries from the list and rerun the code, it selects the contracted dataset:
If we add more countries to the original list and rerun the code, it selects the expanded dataset:
Note: When creating the named range, we entered the formula =Sheet4!$A$1:INDEX(Sheet4!$A:$A, COUNTA(Sheet4!$A:$A)) on the ‘Refers to’ box instead of a static cell reference.
The formula generates a reference to all the non-empty cells in column A of Sheet4, starting from A1 and ending at the last non-empty cell in column A.
If you want to exclude the header row from the selection, you can modify the formula to start from cell A2.
Example #2: Use the ‘Range.CurrentRegion’ Property to Select a Dynamic Range
The ‘Range.CurrentRegion’ property produces a range enclosed by any combination of blank columns and rows.
Assume you have the dataset below on Sheet5:
Notice the active cell B4 is within the dataset.
You can use the code below to select the entire dataset:
Sub SelectCurrentRegion()
Worksheets("Sheet5").Activate
Range("B4").CurrentRegion.Select
End Sub
When you run the code, it selects the current region:
The code works if the dataset contracts or expands as data is removed or added.
Note: This method will not work if you want to exclude the header row from the selection.
Example #3: Use the ‘Worksheet.UsedRange’ Property to Select a Dynamic Range
The ‘Worksheet.UsedRange’ property produces a range object representing all the cells that have contained a value at any time. A used range includes any cell on a worksheet that has ever been used. For example, if you enter a value in cell B2 and then delete it, the cell B2 is considered used.
Suppose you have the cell range below representing all used cells on Sheet6:
You can use the VBA code below to select the used range:
Sub SelectUsedRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet6")
ws.Activate
ws.UsedRange.Select
End Sub
When you run the code, it selects the used range:
If you add data to the worksheet and the used range expands, the code will still work and select the expanded range.
The downside of this method is that if you remove data from the dataset and it contracts, the code still selects the original range:
Note: This method will not work if you want to exclude the header row from the selection.
Example #4: Use the ‘Range.End’ Property to Select a Dynamic Range
The ‘Range.End’ property gets a range object representing the cell at the end of the region containing the source range. Using this property in code is equivalent to pressing END+DOWN ARROW, END+UP ARROW, END+RIGHT ARROW, or END+LEFT ARROW.
Suppose you have the dataset below on the active sheet:
You can use the VBA code below to select the entire dataset from cell A1:
Sub SelectCellsWithValues()
Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
End Sub
When you run the code, it selects the entire dataset:
If you want to exclude the header row from the selection, modify the code to start at cell A2, and it will exclude the header row from the selection:
In this tutorial, I showed you several examples of selecting a single cell, a static, and a dynamic range. I hope you found the tutorial helpful.
Other Exel articles you may also like: