All my avid readers from Computer Programming background 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 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 small task or a big software. Performance matters. None of us like below screen while watching an exciting Cricket Match on Internet or a Movie etc. Then why to 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.

``````

'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
``````

### 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.