CELLS() and RANGE() – Are they same? Are they Excel VBA Object?

.

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:
 
Çell-traverse-using-loop
 

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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

5 Comments

  1. Girish S

    This blog helps to know advanced topics in Excel very much.

    Reply
  2. Rudra

    Thanks Vishwa,
    Thanks for explaining it. Really a useful article.

    Reply
  3. Hareesh

    Pls correct this

    Using Cells()
    Value of same Cell B4 can be referred using Cells like this: Cells(2, 4).Value where 2 : is Row Number and 4 : is the column

    it should be cell(4,2) to select B4

    Reply
    • Vishwamitra Mishra

      Thank you so much for pointing out this error. Thank you! Fixed.

      Reply
  4. Dietrich Schmitz

    Great help, thanks!

    Reply

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest