Reference cells and ranges

You can specify a cell or range of cells in one statement that identifies the range and also changes a property or applies a method.

A Range object in Visual Basic can be either a single cell or a range of cells.

Refer to All the Cells

When you apply the Cells property to a worksheet without specifying an index number, the method returns a Range object that represents all the cells on the worksheet.

'Clear everything from all the cells on Sheet1 in the active workbook.
Worksheets("Sheet1").Cells.Clear

'Clear only the formatting from all the cells on Sheet1 in the active workbook.
Worksheets("Sheet1").Cells.ClearFormats

'Clear formulas and values from all the cells on Sheet1 in the active workbook.
Worksheets("Sheet1").Cells.ClearContents

'Clear comments from all the cells on Sheet1 in the active workbook.
Worksheets("Sheet1").Cells.ClearComments

'Clear Hyperlinks from all the cells on Sheet1 in the active workbook.
Worksheets("Sheet1").Cells.ClearHyperlinks

Using A1 Reference Style

Refer to a cell or range of cells in the A1 reference style by using the Range property.

Syntax

Range (Cell1, Cell2)

Parameters

Cell1: Required. The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they're ignored.
Use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro.
Cell2: Optional. The cell in the upper-left and lower-right corner of the range. Can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.

Example

'Cell A1
Range("A1")

'Cells A1 through C5
Range("A1:C5")

'A multiple-area
Range("C2:D6,G9:J12")

'Column A
Range("A:A")

'Row 1
Range("1:1")

'Columns A through C
Range("A:C")

'Rows 1 through 5
Range("1:5")

'Rows 1, 3, and 8
Range("1:1,3:3,8:8")

'Columns A, C, and F
Range("A:A,C:C,F:F")

'Column F and Row 5
Range("F:F,5:5")

'Range A3:F8 and Range B1:D7 intersection area B3:D7
Range("A3:F8 B1:D7")

'Cell A1 through named range (LastCell)
Range("A1:LastCell")

Refer to Cells by Using Index Numbers

Use the Cells property to refer to a single cell by using row and column index numbers. This property returns a Range object that represents a single cell.

Syntax

Cells(RowIndex, ColumnIndex)

Parameters

RowIndex: Optional. The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they're ignored.

Use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro.

ColumnIndex: Optional. The cell in the upper-left and lower-right corner of the range. Can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.

Example

No parameters

'Active sheet cells address $1:$1048576 (Test in Excel 2019)
MsgBox ActiveSheet.Cells.Address

ColumnIndex omitted

When ColumnIndex is omitted, Cells(RowIndex) returns the index number of the range, the order is left to right, top to bottom.

Sub CellsRowIndex()
    Dim i As Integer
    With Range("B2:D6")
        For i = 1 To .Cells.Count
            .Cells(i) = i
        Next i
    End With
End Sub

RowIndex omitted

When RowIndex is omitted, the default value of the RowIndex parameter is 1.

ActiveSheet.Cells(, 3).Address

=

ActiveSheet.Cells(1, 3).Address

Variables

The Cells property works well for looping through a range of cells, because you can substitute variables for the index numbers, as shown in the following example.

Sub CellsLoop()
    Dim i As Integer
    For i = 1 To 100
        ActiveSheet.Cells(i, 1).Value = i
    Next i
End Sub

Refer to Cells by Using Shortcut Notation

Use either the A1 reference style or a named range within brackets as a shortcut for the Range property. You don't have to type the word "Range" or use quotation marks, as shown in the following examples.

Worksheets("Sheet1").[A1:B5].ClearContents

Dim MyRange As Range
Set MyRange = [A1:D6]

[A1:D6 C4:E8] = 8

Refer to Cells by Using Offset Property

Syntax

expression.Offset (RowOffset, ColumnOffset)
expression A variable that represents a Range object.

Parameters

RowOffset
Optional. The number of rows—positive, negative, or 0 (zero)—by which the range is to be offset. Positive values are offset downward, and negative values are offset upward. The default value is 0.

ColumnOffset
Optional. The number of columns—positive, negative, or 0 (zero)—by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left. The default value is 0.

If RowOffset or ColumnOffset are 0 (zero) they can be omitted.

Example

Select Range D4:E6

Range("A1:B3").Offset(3, 3).Select

Select cell A4

Range("A1").Offset(3).Select

Refer to Cells by Using Resize Property

Resizes the specified range. Returns a Range object that represents the resized range.

Syntax

expression.Resize (RowSize, ColumnSize)

expression An expression that returns a Range object.

Parameters

Example

Range("A1").resize(4,4).Select

Refer to Rows and Columns

Use the Rows property or the Columns property to work with entire rows or columns. These properties return a Range object that represents a range of cells.

The following table illustrates some row and column references using the Rows and Columns properties.

Refer to a Named Range

The following example refers to the range named "MyRange" in the workbook named "MyWorkBook.xls."

Sub FormatRange() 
    Range("MyWorkBook.xls!MyRange").Font.Italic = True 
End Sub

The following example refers to the worksheet-specific range named "Sheet1!Sales" in the workbook named "Report.xls."

Sub FormatSales() 
    Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin 
End Sub