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