Hello friends,
 
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:

#1.

Provide the Range across Rows. It means column will remain same but data will be spread in all the rows of the same column.

#2.

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.

 

Across Rows-Column

 

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.

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!