Select Range in Excel Using VBA

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:

Dataset to select range using VBA

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:

VBA to Select one cell

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:

Dataset to select range using VBA

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:

VBA OFFSET to select a cell

You can also select a cell range using the ‘Range.Offset’ property. 

Suppose you have the dataset below on the active worksheet:

Dataset to select range using VBA

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:

VBA OFFSET to select range
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:

Dataset to select range using VBA

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:

VBA to select acticate a cell

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.’

VBA Runtime error 1004

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:

Dataset to select range using VBA

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:

VBA to select a range

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:

Dataset to select range using VBA

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:

dynamic dataset to select using VBA

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:

VBA select dataset named range

If we remove, say, four countries from the list and rerun the code, it selects the contracted dataset:

VBA selects contracted dataset

If we add more countries to the original list and rerun the code, it selects the expanded dataset:

VBA selects 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.

Creating named range

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:

Dataset to select range using VBA

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:

VBA select current range

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:

Dataset to select range using VBA

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:

VBA selects 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:

VBA selects used range even after deleting rows

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:

Dataset to select range using VBA

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:

VBA select 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:

VBA selects range without header

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment