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" & ).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

Comments

  1. Greg says

    This is a very helpful site. One of the problems is that in some cases the “Range(Cells(3, 3), Cells(8, 7)).Select” works and in some it does not. I can not figure out the difference between when it works and when it does not.

  2. Amr says

    Dears,

    I need help for this coding how can make change with this code to be ask me for select the range no to be static column like “H , I or B” i need every time to ask me and i will choose the range .

    Sub Goal_Seek_Range()
    Dim I As Integer
    For I = 6 To 14
    Range(“H” & I).GoalSeek Goal:=Range(“I” & I).Value, ChangingCell:=Range(“B” & I)
    Next I
    End Sub

  3. Mike Rempel says

    Can you use a count function inside an OFFSET function in VBA? My range width is static but the height varies based on formulas in the cells.

Leave a Reply

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