Select a Range with Excel VBA

One of the basic things you need to do in Excel VBA is to select a specific range to do something with it. This course will show you how to use Range, Cells, Offset, Resize and Union to select a range in Excel VBA. If you want to learn select all cells that contain a specific type of data, learn this article: Go To Special with VBA.

Select all the cells of a worksheet

Cells.Select

Select a cell

Cells(4, 5).Select

=

Range("E4").Select

It seems Range() is much easier to read and Cells() is easier to use inside a loop.

Select a set of contiguous cells

Range("C3:G8").Select

=

Range(Cells(3, 3), Cells(8, 7)).Select

=

Range("C3", "G8").Select

Select a set of non contiguous cells

Range("A2,A4,B5").Select

Select a set of non contiguous cells and a range

Range("A2,A4,B5:B8").Select

Select a named range

Range("MyRange").Select

=

Application.Goto "MyRange"

Select an entire row

Range("6:6").Select

=

Rows("6:6").Select

Select an entire column

Range("D:D").Select

=

Columns("D:D").Select

Select the last cell of a column of contiguous data

Range("A1").End(xlDown).Select

When this code is used with the following example table, cell A3 will be selected.

Select the blank cell at bottom of a column of contiguous data

Range("A1").End(xlDown).Offset(1,0).Select

When this code is used with the following example table, cell A4 will be selected.

Select an entire range of contiguous cells in a column

Range("A1", Range("A1").End(xlDown)).Select

When this code is used with the following example table, range A1:A3 will be selected.

Select the last blank cell in column A

Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Select

When this code is used with the following example table, range A9 will be selected.

Select an entire range of non-contiguous cells in a column

Range("A1", Range("A" & Rows.Count).End(xlUp)).Select

Note: This VBA code supports Excel 2003 to 2013.

When this code is used with the following example table, range A1:A8 will be selected.

Select a rectangular range of cells around a cell

Range("A1").CurrentRegion.Select

Select a cell relative to another cell

ActiveCell.Offset(5, 5).Select

Range("D3").Offset(5, -1).Select

Select a specified range, offset It, and then resize It

Range("A1").Offset(3, 2).Resize(3, 3).Select

When this code is used with the following example table, range C4:E6 will be selected.

Union of two or more ranges

Union(Range("A1:A8"), Range("D7"), Range("A1:C2")).Select

Select used range

ActiveSheet.UsedRange.Select

When this code is used with the following example table, range A1:F7 will be selected.