How to paste a whole array without looping through it in VBA?
As I mentioned in my comment above that you do not need an array to perform the action that you trying to do but still if you want only an array solution then you don't need to go the long way of filling the array in a loop. Directly assign the range's value to the array. It will create a 2D array which you don't need to transpose.
Sub rangearray()
Dim arr
Dim Rng As Range
With ActiveSheet
Set Rng = ActiveSheet.Range("G10:G14")
arr = Rng.Value
.Range("H10").Resize(UBound(arr, 1)).Value = arr
End With
End Sub
you will want to use the transpose worksheet function http://msdn.microsoft.com/en-us/library/office/ff196261.aspx
See below. You have to assign it to the range's value
Sub rangearray()
Dim arr() As Variant
Dim Rng As Range
Dim myCell As Range
Dim i As Integer
Set Rng = ActiveSheet.Range("A1:A5")
For Each myCell In Rng
ReDim Preserve arr(i)
arr(i) = myCell
i = i + 1
Next myCell
ActiveSheet.Range("B1:B5").Value = WorksheetFunction.Transpose(arr)
End Sub