{"id":12139,"date":"2012-06-07T11:57:18","date_gmt":"2012-06-07T11:57:18","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1737"},"modified":"2022-08-06T13:14:28","modified_gmt":"2022-08-06T13:14:28","slug":"excel-range-in-array","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/","title":{"rendered":"How to get Excel Range in Array"},"content":{"rendered":"

Many times while developing an application in Excel VBA, we need all the values stored in an excel Cell Range in an Array variable. So that at any point in time we can access the value from the array, rather than going to Excel Cell and reading it from there. Also, now that you have all the values from a cell range in an array, it is very easy to iterate them and perform any kind of action we want on them.<\/p>\n

VBA Code to store cell range values in an Array variable<\/h1>\n

This is one of the functions I always keep handy. I am sharing it with you guys so that you don’t have to write such functions every time you are working on any excel VBA project. The following function will accept Cell Range as Input and Return a String Array<\/strong> having all the values in that cell Range. <\/p>\n

Step 1.<\/strong> Copy and Paste the below code in Excel Macro Module<\/p>\n

\r\n\r\nPublic Function GetArray(xlRange As Range) As String()\r\n    Dim strArray() As String\r\n    Dim iCounter As Integer\r\n    Dim intCount As Integer\r\n    Dim xlCell As Range\r\n    \r\n    iCounter = 0\r\n    intCount = xlRange.Cells.Count\r\n    \r\n        ReDim strArray(0 To intCount - 1)\r\n        For Each xlCell In xlRange\r\n                strArray(iCounter) = xlCell.Value\r\n                iCounter = iCounter + 1\r\n        Next\r\n    \r\n    GetArray = strArray\r\n \r\nEnd Function\r\n\r\n<\/code><\/pre>\n
\nStep 2.<\/strong> You can use the above Function anywhere in the Excel macro and it will return the String Array.<\/p>\n
\r\nSub GetArray()\r\n    Dim strArr() As String\r\n    strArr() = GetArray(ActiveSheet.Range(\"A1:A5\"))\r\nEnd Sub\r\n<\/code><\/pre>\n
<\/p>\n
I am storing All the values in a Single Dimensional Array. Therefore If you are passing Range of Single Row or a Single Column then all the Values will be Stored as a single array.
\n
For Example: 1. <\/strong>If the Range is “A1:A5”<\/strong> and Array is strArr then Array will be defined as below:
\n<\/p>\n
\"Cell
Excel Cell Range to Array – One Dimensional Array<\/p><\/div>\n
For Example: 2. <\/strong>If the Range is “A1:B5”<\/strong> and Array is strArr then Array will be defined as below:
\n<\/p>\n
\"Range
Excel Range to Array – Single dimensional array<\/p><\/div>\n
Conclusion<\/h2>\n
No matter how many rows and columns are part of the input range you provide, array returned is single dimensional array. This statement in the above function For Each xlCell In xlRange<\/code> traverse by rows. In other words, first it returns all the column values from left to right for the FIRST ROW then SECOND ROW and so on. This is also very clear from the above pictures.
\nNote: It is also possible to store them in 2 Dimensional Array, if you want. You need to define a two Dimensional array to store it.<\/p>\n
If you want the workbook with this Function Mail Me <\/a><\/h3>\n
\nTo Check out more Excel Macro Tutorials, visit Excel Macro Tutorial<\/a><\/strong>\n<\/div>\n<\/span>","protected":false},"excerpt":{"rendered":"
Many times while developing an application in Excel VBA, we need all the values stored in an excel Cell Range in an Array variable. So that at any point in time we can access the value from the array, rather than going to Excel Cell and reading it from there. Also, now that you have […]<\/p>\n","protected":false},"author":45,"featured_media":242429,"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":[1673,1246,2056],"tags":[],"yoast_head":"\nHow to get Excel Range in Array - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"How to store values from an excel range into the array variable. Excel Range into the array. VBA Function to read a range in an Array variable\" \/>\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\/2012\/06\/excel-range-in-array\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to get Excel Range in Array\" \/>\n<meta property=\"og:description\" content=\"How to store values from an excel range into the array variable. Excel Range into the array. VBA Function to read a range in an Array variable\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\" \/>\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=\"2012-06-07T11:57:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-06T13:14:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/06\/Range_To_Array-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"375\" \/>\n\t<meta property=\"og:image:height\" content=\"219\" \/>\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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"How to get Excel Range in Array\",\"datePublished\":\"2012-06-07T11:57:18+00:00\",\"dateModified\":\"2022-08-06T13:14:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\"},\"wordCount\":403,\"commentCount\":11,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Functions\",\"Excel Macro\",\"User Defined Function\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\",\"name\":\"How to get Excel Range in Array - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2012-06-07T11:57:18+00:00\",\"dateModified\":\"2022-08-06T13:14:28+00:00\",\"description\":\"How to store values from an excel range into the array variable. Excel Range into the array. VBA Function to read a range in an Array variable\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Functions\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-functions\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How to get Excel Range in Array\"}]},{\"@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\":\"required name=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:\/\/twitter.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":"How to get Excel Range in Array - Let's excel in Excel","description":"How to store values from an excel range into the array variable. Excel Range into the array. VBA Function to read a range in an Array variable","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\/2012\/06\/excel-range-in-array\/","og_locale":"en_US","og_type":"article","og_title":"How to get Excel Range in Array","og_description":"How to store values from an excel range into the array variable. Excel Range into the array. VBA Function to read a range in an Array variable","og_url":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/","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":"2012-06-07T11:57:18+00:00","article_modified_time":"2022-08-06T13:14:28+00:00","og_image":[{"width":375,"height":219,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/06\/Range_To_Array-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":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"How to get Excel Range in Array","datePublished":"2012-06-07T11:57:18+00:00","dateModified":"2022-08-06T13:14:28+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/"},"wordCount":403,"commentCount":11,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Functions","Excel Macro","User Defined Function"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/","url":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/","name":"How to get Excel Range in Array - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2012-06-07T11:57:18+00:00","dateModified":"2022-08-06T13:14:28+00:00","description":"How to store values from an excel range into the array variable. Excel Range into the array. VBA Function to read a range in an Array variable","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Functions","item":"https:\/\/vmlogger.com\/excel\/excel-functions\/"},{"@type":"ListItem","position":3,"name":"How to get Excel Range in Array"}]},{"@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":"required name=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:\/\/twitter.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\/12139"}],"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=12139"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12139\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242429"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}