Edit

Share via

Range.Cells property (Excel)

  • Article

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

Note

Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

Syntax

expression.Cells

expression A variable that represents a Range object.

Remarks

The return value is a Range consisting of single cells, which allows to use the version of the Item with two parameters and lets For Each loops iterate over single cells.

Because the default member of Range forwards calls with parameters to the Item property, you can specify the row and column index immediately after the Cells keyword instead of an explicit call to Item.

Using Cells without an object qualifier is equivalent to ActiveSheet.Cells.

Example

This example sets the font style for cells B2:D6 on Sheet1 of the active workbook to italic.

With Worksheets("Sheet1").Range("B2:Z100") 
   .Range(.Cells(1, 1), .Cells(5, 3)).Font.Italic = True
End With

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

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

This example demonstrates how Cells changes the behavior of the Item member.

Public Sub PrintRangeAdresses
   Dim columnsRange As Excel.Range
   Set columnsRange = ThisWorkBook.Worksheets("exampleSheet").Range("B2:Z100").Columns
   
   Debug.Print columnsRange.Item(2).Address         'Prints "$C$2:$C$100" 
   Debug.Print columnsRange.Cells.Item(2).Address   'Prints "$C$2" 
   Debug.Print columnsRange.Cells.Item(2,1).Address 'Prints "$B$3"   
End Sub

This example demonstrates how Cells changes the enumeration behavior.

Public Sub PrintAllRangeAdresses
   Dim columnsRange As Excel.Range
   Set columnsRange = ThisWorkBook.Worksheets("exampleSheet").Range("B2:C3").Columns
   
   Dim columnRange As Excel.Range
   For Each columnRange In columnsRange
      Debug.Print columnRange.Address   'Prints "$B$2:$B$3", "$C$2:$C$3"
   Next
   
   Dim cell As Excel.Range
   For Each cell In columnsRange.Cells
      Debug.Print cell.Address          'Prints "$B$2", "$C$2", "$B$3", "$C$3"
   Next  
End Sub

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.