Many of you who are interested in excel Macro or if you have ever tried to read and understand a VBA code, you would have seen two ways of referring a cell in Excel Macro:
To refer value of Cell D4, you can use Range(“B4”).Value. To know more about RANGE() you can read my previous article about Range Object.
Value of same Cell B4 can be referred using Cells like this: Cells(4, 2).Value where 4 : is Row Number and 2 : is the column number. It accepts two numeric value to represent row and column where first number represents row and second one as column as shown in the above example.
Though from the above example it looks like both are same because they are used to refer a cell in excel sheet, but CELLS and RANGE both are not at all same. Find the main differences below:
CELLS is a property but RANGE is an Object
CELLS is a property of a RANGE or WorkSheet or Application Objects while RANGE itself is an Object. Cells returns a Range Object. Cells can be used without any parameter passed or a single parameter passed in it. Since Cells can be a property of WorkSheet or Range object, hence it gives the results based on what is object.
Suppose I have a Range Range(“C3:F10”).
This will return all the cells of the Range(C3:F10) from C3 to F10.
This will return the cell in First Row (1) and second column (2) of the Range(“C3:F10”). Therefore (“C3:F10”).Cells(1, 2) will return Range(“D3”). 3 – First Row of the range and D – second column of the range (starting column is C).
In Cells when there is only one parameter then by default it considers as First row and parameter passed is the column. Therefore Range(“C3:F10”).Cells(3) is equal to Range(“C3:F10”).Cells(1, 3)
Suppose I have a WorkSheet named “Sheet 1”. Or you can use ActiveSheet Object as well. Cells is property of ActiveSheet Object as well.
ActiveSheet.Cells or WorkSheets(“Sheet1”).Cells
This will return all the cells of the Active WorkSheet. For example, if you want to clear the contents of whole WorkSheet then you can use the below statement:
ActiveSheet.Cells(1, 2) or WorkSheets(“Sheet1”).Cells(1, 2)
This will return the Range(“B1”). Row 1 and Column 2 of the WorkSheet.
I have passed the same parameter as I have passed in the above example with RANGE Object. There in that example, it had returned the Cell as Range(“D3”) while here in this example it has returned the cell Range(“B1”) because here the whole sheets is considered as a single Range Object and Cells is providing the property on the whole sheet.
.Cells Property – Where it is used more?
As you know RANGE Object refers a cell or cell range with Row Number and Column Name Cells is most useful when you have to refer Cells/Range in a Loop then Cells is useful because both the parameters are numeric here and you can use them for loop.
Code to traverse cells in Row and Columns both
Sub Cell_Traverse() Dim iRow Dim iCol For iRow = 1 To 5 'traverse across rows For iCol = 1 To 5 - traverese across columns in a Row Cells(iRow, iCol).Value = iRow & " , " & iCol Next Next End Sub
Above code will display in Sheet something like below: