How to Select a Range/Cell using VBA in Excel

Key Notes

• You can use the Range property as well as the Cells property with the Select method to select a range.

Select a Single Cell

To select a single cell, you need to define the cell address using the range, and then you need to use the Select method. Let’s say you want to select cell A1; the code would be:

Range("A1").Select

And if you want to use CELLS, in that case, the code would be:

Cells(1,1).Select

Select a Range of Cells

To select an entire range, you need to define the address of the range and then use the Select method. For example, if you want to select the range A1 to A10, the code would be:

Range("A1:A10").Select

Select Non-Contiguous Range

To select a non-continuous range, you need to use a comma between the cell or range addresses, and then use the Select method. Let’s say you want to select the range A1 to A10 and C5 to C10, the code would be:

Range("A1:A10, C5:C10").Select

And if you want to select single cells that are non-continuous, the code would be:

Range("A1, A5, A9").Select

Select a Column

To select a column, let’s say column A, you need to write code like the following:

Range("A:A").Select

And if you want to select multiple columns, in that case, the code would be like the following:

Range("A:C").Select
Range("A:A, C:C").Select

Select a Row

In the same way, if you want to select a row, let’s say row five, the code would be like the following:

Range("5:5").Select

And for multiple rows, the code would be:

Range("1:5").Select
Range("1:1, 3:3").Select

Select All the Cells of a Worksheet

Let’s say you want to select all the cells in the worksheet, just like you use the keyboard shortcut Ctrl + A. You need to use the following code.

ActiveSheet.Cells.Select
Cells.Select

“Cells” refers to all the cells in the worksheet, and then the Select method selects them.

Select Cells with Data Only

Here, “Cells with Data” means the area of the worksheet that is in use, from the first to the last cell with data, and you can use the following code. Note that UsedRange also includes cells that only have formatting, so it can be a little bigger than your data.

ActiveSheet.UsedRange.Select

Select a Named Range

If you have a named range, you can select it by using its name.

Range("my_range").Select

In the above code, you have the “my_range” named range and then the Select method, and when you run this macro, it selects the specified range.

Select an Excel Table

If you work with Excel tables, you can also select them using the Select method. Let’s say you have a table with the name “Data”; then the code to select that table would be:

Range("Data").Select

Range(“Data”) selects the data of the table without the header row. To select the whole table, including the headers, use Range(“Data[#All]”).Select.

If you want to select a column instead of the entire table, then the code would be like the following:

Range("Data[Amount]").Select

And if you want to select the entire column, including the header, then you can use this code:

Range("Data[[#All],[Amount]]").Select

Using OFFSET to Select a Range

You can also use the OFFSET property to select a cell or a range by navigating from a cell or a range. Let’s suppose you want to select a cell that is four columns right and five rows down from A1 (cell E6); you can use the following code.

Range("A1").Offset(5, 4).Select

Select a Range from a Cell to the Last Row

When the size of your data changes, you can select from the first cell down to the last cell with data. The following code selects from A1 to the last value in column A, and the second line selects the whole block of data around A1 (the same as Ctrl + A inside the data):

Sub select_to_last_row()
    Range("A1", Cells(Rows.Count, "A").End(xlUp)).Select
    Range("A1").CurrentRegion.Select
End Sub

Select a Range on Another Worksheet

You can only select cells on the active sheet. If you try Worksheets(“Sheet2”).Range(“A1”).Select while another sheet is active, you get run-time error 1004 (Select method of Range class failed). Activate the sheet first, or use Application.Goto, which does both in one line:

Sub select_on_another_sheet()
    Worksheets("Sheet2").Activate
    Range("A1:A10").Select
    ' or in one line
    Application.Goto Worksheets("Sheet2").Range("A1:A10")
End Sub

Frequently Asked Questions