All my avid readers from Computer Programming backgrounds must be thinking is it a big deal? Like other programming languages use loop to traverse all the values of an array and spread them across rows or column. Right?
 
If you are thinking so… you are absolutely right. You can do it using the loop.
 
But what if I tell you that there is a very easy and faster way to do so? Excited??
 
Generally, as a developer, we tend to forget the performance aspect while coding… be it a code for a small task or a big software. Performance matters. None of us like the below screen while watching an exciting Cricket Match on Internet or a Movie etc. Then why ignore the performance aspect??
 

 
So let’s learn some faster technique to solve the above task. In the above task performance does not really matters when Array is smaller in size. But when Array size is bigger (like in Thousands..) then you will realize the performance issue.

 
So without wasting any more time let’s come to the point. In this article I will give you both the codes to spread the values of an array towards Rows or Columns. At the end of the article you will find a downloadable which will show you the performance difference.

1. Slower Method – Array values across columns – Using Loop

Below is the traditional code to traverse an array value using loop and using this all the values of an array will be spread across columns.
 

Sub SpreadUsingLoop()
'This function will spread the array values using For loop
    
    Dim myArr(10000) As Variant
    Dim TimeDuration As Long
    Dim StartTime As Date
    Dim EndTime As Date
    
    StartTime = Now 'Start time is captured here
    
'fill values in this array
    For i = 0 To UBound(myArr) - 1
        myArr(i) = "This is my data " & i
    Next
'myArr has 5000 values in it.
'to spread this 5000 values, here we will use loop
    Rows(6).Clear
    
    For i = 0 To UBound(myArr) - 1
        Cells(6, i + 1).Value = myArr(i)
    Next
    EndTime = Now  ' End time is captured
'Calculate the total time duration in seconds
    TimeDuration = DateDiff("s", CDate(StartTime), CDate(EndTime))
'Show the time taken in Message Box
    MsgBox TimeDuration & " Second(s)"
End Sub

2. Faster Method – Array values across columns – Using Array to Range

Below is the BEST way to spread an array values across columns.

 

Sub SpreadUsingArrayToRange()
    
'This function will spread the array values using For Array to Range
    
    Dim myArr(10000) As Variant
    Dim TimeDuration As Long
    Dim StartTime As Date
    Dim EndTime As Date
    
    StartTime = Now 'Start time is captured here
    
'fill values in this array
    For i = 0 To UBound(myArr) - 1
        myArr(i) = "This is my data " & i
    Next
'myArr has 5000 values in it.
'to spread this 10000 values, here we will use array to range method
    Rows(11).Clear
    Range(Cells(11, 1), Cells(11, UBound(myArr))).Value = myArr
    EndTime = Now  ' End time is captured
'Calculate the total time duration in Seconds
    TimeDuration = DateDiff("s", CDate(StartTime), CDate(EndTime))
'Show the time taken in Message Box
    MsgBox TimeDuration & " Second(s)"
    
End Sub

 

How is the second method better?

i) Simpler in coding – Single line code

In the second method you need not to write a looping code of 3 lines with a traversing variable “i”. Thus it makes your coding easy, short and understandable.

ii) Faster – Better Performance

This is the Major benefit of the second method. This method is way faster than the First one. For 5000 Records you can see the difference by running both the codes. My observation is that – First code always takes 2 to 3 seconds for 5000 records while the second method completes in ZERO seconds.