Cells Method

Applies To

Application Object, Range Object, Worksheet Object.

Description

Accessor. Returns a Range object that represents a single cell (Syntax 1 and 2) or a collection of cells (Syntax 3). The action of the Cells method depends on the object to which it is applied, as shown in the following table.

Syntax 1

object.Cells(rowIndex, columnIndex)

Syntax 2

object.Cells(rowIndex)

Syntax 3

object.Cells

object

Optional for Application, required for Worksheet and Range. The object that contains the cells. If you specify the Application object (or omit the object qualifier), the Cells method applies to the active sheet in the active workbook. If the active sheet is not a worksheet, this method fails.

rowIndex

Required for Syntax 1. The row number of the cell you want to access, starting with 1 for row 1 (for Application and Worksheet) or the first row in the Range.

Required for Syntax 2. A long integer specifying the index number of the cell you want to access, in row-major order. A1 is Cells(1), A2 is Cells(257) for Application and Worksheet; Range.Cells(1) is the top left cell in the Range.

columnIndex

Required for Syntax 1. A number or string indicating the column number of the cell you want to access, starting with 1 or "A" for column A (for Application or Worksheet) or the first column in the Range.

Remarks

Syntax 1 uses a row number and a column number or letter as index arguments. For more information about this syntax, see the Range object.

Syntax 2 uses a single number as an index argument. The index is 1 for cell A1, 2 for cell B1, 3 for cell C1, 257 for cell A2, and so on.

The rowIndex and columnIndex arguments are relative offsets when you apply the Cells method to a Range object. In other words, specifying a rowIndex of 1 returns cells in the first row of the range, not the first row of the worksheet. For example, if the selection is cell C3, then Selection.Cells(2, 2) returns cell D4 (you can use the method to index outside the original range).

If you apply Syntax 3 to a Range, it returns the same Range object (in other words, it does nothing).

If you apply Syntax 3 to a Worksheet, it returns a collection of all the cells in the worksheet (all the cells, not just the cells that are currently in use).

See Also

Range Method.

Example

This example sets the font size of cell C5 on Sheet1 to 14 points.

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

This example clears the formula in cell one on Sheet1.

Worksheets("Sheet1").Cells(1).ClearContents

This example sets the font for every cell on Sheet1 to 8-point Arial.

With Worksheets("Sheet1").Cells.Font
    .Name = "Arial"
    .Size = 8
End With

This example loops through cells A1:J4 on Sheet1. If one of the cells has a value less than .001, the example replaces that value with zero (0).

For rwIndex = 1 to 4
    For colIndex = 1 to 10
        If Worksheets("Sheet1").Cells(rwIndex, colIndex) < .001 Then
            Worksheets("Sheet1").Cells(rwIndex, colIndex).Value = 0
        End If
    Next colIndex
Next rwIndex

This example sets the font of cells A1:C5 on Sheet1 to italic.

Worksheets("Sheet1").Activate
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True

This example scans a column of data named myRange. If a cell has the same value as the cell above it, the example displays the address of the cell that contains the duplicate data.

Set r = Range("myRange")
For n = 1 To r.Rows.Count
    If r. Cells(n, 1) = r. Cells(n + 1, 1) Then
        MsgBox "Duplicate data in " & r. Cells(n + 1, 1).Address
    End If
Next n