Dear Friends,

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:

Using Range()

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.

Using Cells()

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, 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.

Range.Cells

Suppose I have a Range Range(“C3:F10”).

Range(“C3:F10”).Cells

This will return all the cells of the Range(C3:F10) from C3 to F10.

Range(“C3:F10”).Cells(1, 2)

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).

Range(“C3:F10”).Cells(3)

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)

WorkSheet.Cells

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.ClearContents

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 is it used more often?

As you know RANGE Object refers a cell or cell range with Row Number and Column Name. Cells is the most useful to use 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.

Examples:

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:
 

 

Note: Using Range() traversing in Loop in Rows and Columns both is not possible like above using Cells.