{"id":12189,"date":"2014-04-11T17:46:39","date_gmt":"2014-04-11T17:46:39","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=3290"},"modified":"2022-09-14T13:03:08","modified_gmt":"2022-09-14T13:03:08","slug":"array-to-rows","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/","title":{"rendered":"Spreading an Array values across Rows in Excel &#8211; VBA"},"content":{"rendered":"<p>This is an extension to my <a href=\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-range-excel-macro\/\"> previous Article. <\/a> Where we learned the best way to spread array values across columns in Excel. Here I will teach you how to spread Array values across Rows in Columns.<\/p>\n<h3>How it works? How it is different from the previous code? <\/h3>\n<p>In the previous code, there are TWO changes that need to be done. Just changing the Range address across Rows will not help in achieving this distribution of array values across rows. This is the main reason for writing this as a separate article. Following are the two changes that need to be done to distribute the array values across rows:<\/p>\n<ol>\n<li>Provide the Range across Rows. It means the column will remain the same but data will be spread in all the rows of the same column. <\/li>\n<li>Second change that is required is to transpose the array. If you do not transpose this single-dimensional array before assigning it to the array, the first value of the array will be repeated across all the rows. You can try doing this.<\/li>\n<p>&nbsp;<\/p>\n<div align=center>\n<img decoding=\"async\" src=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png\" alt=\"Across Rows-Column\" title=\"Across Rows-Column\" width=\"300\" height=\"225\" class=\"size-full wp-image-3291\" \/><\/div>\n<p>&nbsp;<\/p>\n<h3>Code for Array values across rows\u2013 Using Array to Range<\/h3>\n<pre><code class=\"language-vbnet\">\r\nSub SpreadUsingArrayToRange()\r\n    'This function will spread the array values using For Array to Range\r\n    Dim i As Integer\r\n    Dim myArr(10000) As Variant\r\n    \r\n    'fill values in this array\r\n    For i = 0 To UBound(myArr) - 1\r\n        myArr(i) = \"This Is my data \" & i\r\n    Next\r\n    'myArr has 10000 values in it.\r\n    'to spread these 10000 values, here we will use an array to range\r\n    'To spread it across rows, We just need to transpose the array before assigning\r\n    Range(\"A1:A\" & UBound(myArr)).Value = Application.Transpose(myArr)\r\nEnd Sub\r\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h3>To know more about Range and Array distribution in detail, <a href=\"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/\">you can read this article.<\/a> <\/h3>\n<span class=\"et_bloom_bottom_trigger\"><\/span>","protected":false},"excerpt":{"rendered":"<p>This is an extension to my previous Article. Where we learned the best way to spread array values across columns in Excel. Here I will teach you how to spread Array values across Rows in Columns. How it works? How it is different from the previous code? In the previous code, there are TWO changes [&hellip;]<\/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,1682],"tags":[],"class_list":["post-12189","post","type-post","status-publish","format-standard","hentry","category-macro","category-popular-articles"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v23.5 (Yoast SEO v23.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Spreading an Array values across Rows in Excel - VBA - Let&#039;s excel in Excel<\/title>\n<meta name=\"description\" content=\"Spreading Array values in excel VBA. Spreading array to excel sheet. Spread array values across rows and columns 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\/04\/array-to-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spreading an Array values across Rows in Excel - VBA\" \/>\n<meta property=\"og:description\" content=\"Spreading Array values in excel VBA. Spreading array to excel sheet. Spread array values across rows and columns in Excel VBA\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"Let&#039;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-04-11T17:46:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-09-14T13:03:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.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\/2014\/04\/array-to-rows\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Spreading an Array values across Rows in Excel &#8211; VBA\",\"datePublished\":\"2014-04-11T17:46:39+00:00\",\"dateModified\":\"2022-09-14T13:03:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/\"},\"wordCount\":214,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png\",\"articleSection\":[\"Excel Macro\",\"Popular Articles\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/\",\"name\":\"Spreading an Array values across Rows in Excel - VBA - Let&#039;s excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png\",\"datePublished\":\"2014-04-11T17:46:39+00:00\",\"dateModified\":\"2022-09-14T13:03:08+00:00\",\"description\":\"Spreading Array values in excel VBA. Spreading array to excel sheet. Spread array values across rows and columns in Excel VBA\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#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\":\"Spreading an Array values across Rows in Excel &#8211; VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let&#039;s excel in Excel\",\"description\":\"Let&#039;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 &amp; 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":"Spreading an Array values across Rows in Excel - VBA - Let&#039;s excel in Excel","description":"Spreading Array values in excel VBA. Spreading array to excel sheet. Spread array values across rows and columns 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\/04\/array-to-rows\/","og_locale":"en_US","og_type":"article","og_title":"Spreading an Array values across Rows in Excel - VBA","og_description":"Spreading Array values in excel VBA. Spreading array to excel sheet. Spread array values across rows and columns in Excel VBA","og_url":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/","og_site_name":"Let&#039;s excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2014-04-11T17:46:39+00:00","article_modified_time":"2022-09-14T13:03:08+00:00","og_image":[{"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.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\/2014\/04\/array-to-rows\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Spreading an Array values across Rows in Excel &#8211; VBA","datePublished":"2014-04-11T17:46:39+00:00","dateModified":"2022-09-14T13:03:08+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/"},"wordCount":214,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png","articleSection":["Excel Macro","Popular Articles"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/","url":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/","name":"Spreading an Array values across Rows in Excel - VBA - Let&#039;s excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png","datePublished":"2014-04-11T17:46:39+00:00","dateModified":"2022-09-14T13:03:08+00:00","description":"Spreading Array values in excel VBA. Spreading array to excel sheet. Spread array values across rows and columns in Excel VBA","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/04\/Across-Rows-Column.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2014\/04\/array-to-rows\/#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":"Spreading an Array values across Rows in Excel &#8211; VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let&#039;s excel in Excel","description":"Let&#039;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 &amp; 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\/12189"}],"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=12189"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12189\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12189"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12189"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12189"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}