Cells Property

Microsoft Excel Visual Basic

Show All

Cells Property

       

Cells Property as it applies to the Application object.

Returns a Range object that represents all the cells on the active worksheet. If the active document isn’t a worksheet, this property fails. Read-only.

expression.Cells

expression   Required. An expression that returns an Application object.

Cells Property as it applies to the Range object.

Returns a Range object that represents the cells in the specified range. Read-only.

expression.Cells

expression   Required. An expression that returns a Range object.

Cells Property as it applies to the Worksheet object.

Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).  Read-only.

expression.Cells

expression   Required. An expression that returns a Worksheet object.

Remarks

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword. For more information, see the Item property and the examples for this topic.

Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet.

Example

This example sets the font size for 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 and font size 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 a cell contains a value less than 0.001, the example replaces that value with 0 (zero).

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

This example sets the font style for 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 immediately 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