{"id":4209,"date":"2014-07-26T17:59:10","date_gmt":"2014-07-26T17:59:10","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=4209"},"modified":"2017-09-01T17:39:26","modified_gmt":"2017-09-01T17:39:26","slug":"cells-and-range","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/","title":{"rendered":"CELLS() and RANGE() – Are they same? Are they Excel VBA Object?"},"content":{"rendered":"

Dear Friends,<\/p>\n

\nMany 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:\n<\/p>\n

Using Range()<\/h2>\n

To refer value of Cell D4, you can use Range(“B4”).Value<\/strong>. To know more about RANGE()<\/strong> you can read my previous article about Range Object.<\/a><\/p>\n

Using Cells()<\/h2>\n

Value of same Cell B4 can be referred using Cells<\/strong> like this: Cells(4, 2).Value<\/i><\/strong> 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.<\/p>\n

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:
\n <\/p>\n

CELLS is a property but RANGE is an Object<\/h1>\n

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.<\/p>\n

Range.Cells <\/h2>\n

Suppose I have a Range Range(“C3:F10”)<\/i><\/strong>.<\/p>\n

Range(“C3:F10”).Cells<\/h3>\n

This will return all the cells of the Range(C3:F10) from C3 to F10.<\/p>\n

Range(“C3:F10”).Cells(1, 2)<\/h3>\n

This will return the cell in First Row (1) and second column (2) of the Range(“C3:F10”)<\/strong>. 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).<\/p>\n

Range(“C3:F10”).Cells(3)<\/h3>\n

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)<\/strong> is equal to Range(“C3:F10”).Cells(1, 3)<\/strong><\/p>\n

WorkSheet.Cells <\/h2>\n

Suppose I have a WorkSheet named “Sheet 1”. Or you can use ActiveSheet Object as well. Cells is property of ActiveSheet Object as well.<\/p>\n

ActiveSheet.Cells or WorkSheets(“Sheet1”).Cells<\/h3>\n

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:<\/p>\n

\r\nActiveSheet.Cells.ClearContents\r\n<\/code><\/pre>\n
ActiveSheet.Cells(1, 2) or WorkSheets(“Sheet1”).Cells(1, 2)<\/h3>\n
This will return the Range(“B1”). Row 1 and Column 2 of the WorkSheet.<\/p>\n
\nI 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.<\/p>\n
.Cells Property – Where is it used more often?<\/h1>\n
As you know RANGE Object refers<\/a> a cell or cell range with Row Number<\/strong> and Column Name<\/strong>. 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.<\/p>\n
Examples:<\/h1>\n
Code to traverse cells in Row and Columns both<\/h2>\n
\r\nSub Cell_Traverse()\r\nDim iRow\r\nDim iCol\r\n\r\nFor iRow = 1 To 5 'traverse across rows\r\n    For iCol = 1 To 5 - traverese across columns in a Row\r\n        Cells(iRow, iCol).Value = iRow & \" , \" & iCol\r\n    Next\r\nNext\r\nEnd Sub\r\n<\/code><\/pre>\n
Above code will display in Sheet something like below:
\n 
\n\"\u00c7ell-traverse-using-loop\"
\n <\/p>\n
Note: Using Range() traversing in Loop in Rows and Columns both is not possible like above using Cells.<\/h2>\n<\/span>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1679,1676],"tags":[],"class_list":["post-4209","post","type-post","status-publish","format-standard","hentry","category-macro","category-excel-macro-beginner","category-excel-tips"],"yoast_head":"\nCELLS() and RANGE() - Are they same? Are they Excel VBA Object? - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"Difference between Range and Cells in Excel VBA. What is Range () in Excel VBA. What is Cells () in Excel VBA. How to use Cells () in Excel VBA.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CELLS() and RANGE() - Are they same? Are they Excel VBA Object?\" \/>\n<meta property=\"og:description\" content=\"Difference between Range and Cells in Excel VBA. What is Range () in Excel VBA. What is Cells () in Excel VBA. How to use Cells () in Excel VBA.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2014-07-26T17:59:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-09-01T17:39:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"CELLS() and RANGE() – Are they same? Are they Excel VBA Object?\",\"datePublished\":\"2014-07-26T17:59:10+00:00\",\"dateModified\":\"2017-09-01T17:39:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\"},\"wordCount\":587,\"commentCount\":5,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro\",\"Excel Macro Beginner\",\"Excel Tips\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\",\"name\":\"CELLS() and RANGE() - Are they same? Are they Excel VBA Object? - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2014-07-26T17:59:10+00:00\",\"dateModified\":\"2017-09-01T17:39:26+00:00\",\"description\":\"Difference between Range and Cells in Excel VBA. What is Range () in Excel VBA. What is Cells () in Excel VBA. How to use Cells () in Excel VBA.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro\",\"item\":\"https:\/\/vmlogger.com\/excel\/macro\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"CELLS() and RANGE() – Are they same? Are they Excel VBA Object?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"CELLS() and RANGE() - Are they same? Are they Excel VBA Object? - Let's excel in Excel","description":"Difference between Range and Cells in Excel VBA. What is Range () in Excel VBA. What is Cells () in Excel VBA. How to use Cells () in Excel VBA.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/","og_locale":"en_US","og_type":"article","og_title":"CELLS() and RANGE() - Are they same? Are they Excel VBA Object?","og_description":"Difference between Range and Cells in Excel VBA. What is Range () in Excel VBA. What is Cells () in Excel VBA. How to use Cells () in Excel VBA.","og_url":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2014-07-26T17:59:10+00:00","article_modified_time":"2017-09-01T17:39:26+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"CELLS() and RANGE() – Are they same? Are they Excel VBA Object?","datePublished":"2014-07-26T17:59:10+00:00","dateModified":"2017-09-01T17:39:26+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/"},"wordCount":587,"commentCount":5,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro","Excel Macro Beginner","Excel Tips"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/","url":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/","name":"CELLS() and RANGE() - Are they same? Are they Excel VBA Object? - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2014-07-26T17:59:10+00:00","dateModified":"2017-09-01T17:39:26+00:00","description":"Difference between Range and Cells in Excel VBA. What is Range () in Excel VBA. What is Cells () in Excel VBA. How to use Cells () in Excel VBA.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2014\/07\/cells-and-range\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro","item":"https:\/\/vmlogger.com\/excel\/macro\/"},{"@type":"ListItem","position":3,"name":"CELLS() and RANGE() – Are they same? Are they Excel VBA Object?"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4209"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=4209"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4209\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=4209"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=4209"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=4209"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}