Range.Cells Property (Excel)

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

expression .Cells

expression A variable that represents a Range object.

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.

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

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

Sample code provided by: Holy Macro! Books, Holy Macro! It’s 2,500 Excel VBA Examples | About the Contributor

This example looks through column C, and for every cell that has a comment, it puts the comment text into column D and deletes the comment from column C.

Sub SplitComments()
   'Set up your variables
   Dim cmt As Comment
   Dim iRow As Integer
   'Go through all the cells in Column C, and check to see if the cell has a comment.
   For iRow = 1 To WorksheetFunction.CountA(Columns(3))
      Set cmt = Cells(iRow, 3).Comment
      If Not cmt Is Nothing Then
         'If there is a comment, paste the comment text into column D and delete the original comment.
         Cells(iRow, 4) = Cells(iRow, 3).Comment.Text
         Cells(iRow, 3).Comment.Delete
      End If
   Next iRow
End Sub

Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.