Range.Cells property (Excel)
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 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.