Select a Range In 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 article will show you how to use Range, Cells, Offset and Resize to select a range in Excel 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("1:1").Select

Select an entire column

Range("A:A").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 a Range In Excel VBA Example 1

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 a Range In Excel VBA Example 2

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 a Range In Excel VBA Example 3

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 Range In Excel VBA Example 4

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.
Select a Range In Excel VBA Example 5

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>

6 comments
  1. RA
    Raffa

    Very very helpful. Thanks!!

More comments