Range.Cells property (Excel)

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

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 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 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 n

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

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.