Skip to main content

Range.Cells property

Table of contents
  1. Syntax
  2. Remarks
  3. Example

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



expression A variable that represents a Range object.


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.


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 

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"
   Dim cell As Excel.Range
   For Each cell In columnsRange.Cells
      Debug.Print cell.Address          'Prints "$B$2", "$C$2", "$B$3", "$C$3"
End Sub

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>