Range.Cells Property (Excel)
Returns a Range object that represents the cells in the specified range.
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.
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
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