Spreading an Array values across Rows in Excel – VBA
This is an extension to my previous Article. Where we learnt a best way to spread an array values across columns in Excel. Here I will teach you how to spread an Array values across Rows in Column.
How it works? How it is different from the previous code?
In the previous code there are TWO changes which needs to be done. Just by changing the Range address across Rows will not help in achieving this distribution of array values across rows. This is the main reason of writing this as a separate article. Following are the two changes which needs to be done to distribute the array values across rows:
Provide the Range across Rows. It means column will remain same but data will be spread in all the rows of the same column.
Second change which 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.
Code for Array values across rows– Using Array to Range
Sub SpreadUsingArrayToRange() 'This function will spread the array values using For Array to Range Dim i As Integer Dim myArr(10000) As Variant 'fill values in this array For i = 0 To UBound(myArr) - 1 myArr(i) = "This is my data " & i Next 'myArr has 10000 values in it. 'to spread this 10000 values, here we will use array to range 'To spread it across rows, We just need to transpose the array before assigning Range("A1:A" & UBound(myArr)).Value = Application.Transpose(myArr) End Sub
To know more about Range and Array distribution in detail, you can read this article.
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box