What is VBA Range.Cells and How Do You Use It?

This property can be used to select a Range. It returns a Range object that represents the cells. It refers to the cells of the specified range object, which can be a worksheet or a range of cells. It requires two parameter values “RowIndex” and “ColumnIndex”.

Read on to see some ways you can use Range.Cells…

Example #1 – Select all the cells of the active worksheet

 Cells.Select 

Example #2 – To use the cells as parameters in the Range property.  The following code fills in the value of “1” to the range “A2:E4”.

Range(Cells(2,1),Cells(4,5)) = 1

Example #3 – To select a cell on the active worksheet, use either of these codes:

 ActiveSheet.Cells(5, 4).Select 

Or

 ActiveSheet.Range("D5").Select 

Example #4 – To select a cell on another worksheet, use either of these statements:

Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(5, 5)

Or

Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E5"))

Example #5 – To select a cell on a worksheet in a different workbook, use either of these statements:

Application.Goto Workbooks("BOOKTWO.XLS").Sheets("Sheet1").Cells(5, 6)

or

Application.Goto Workbooks("BOOKTWO.XLS").Sheets("Sheet1").Range("F5")

Example #6 – Copy the comments from one column to another column

Sub TransferComments()
 Dim strCmt As Comment
 Dim iRow As Integer

 'Go through all the cells in Column C, and check if the cell has a comment.
 For iRow = 1 To WorksheetFunction.CountA(Columns(3))
 Set StrCmt = Cells(iRow, 3).Comment
 If Not cmt Is Nothing Then

 'If there is a comment, paste the comment text into column D and remove the original comment from column C.
 Cells(iRow, 4) = Cells(iRow, 3).Comment.Text
 Cells(iRow, 3).Comment.Delete
 End If
 Next iRow
End Sub

Before running the script (Cells with items Soda, Juice and Beer are indicated with comments “Sold Out”)

After running the script (The cells with comments were copied to “Remarks” column and the original comments were removed)

If you want to learn how to maximize your use of range.select for selecting a range of cells, read more here.

Leave a Reply

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