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 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:

1. 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.
2. 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.

 



 

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 these 10000 values, here we will use an 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.