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.SelectExample #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)) = 1Example #3 – To select a cell on the active worksheet, use either of these codes:
ActiveSheet.Cells(5, 4).SelectOr
ActiveSheet.Range("D5").SelectExample #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 SubBefore 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.