I have this code which will populate an array
Sub rangearray()
Dim arr() As Variant
Dim Rng As Range
Dim myCell As Range
Dim i As Integer
Set Rng = ActiveSheet.Range("G10:G14")
For Each myCell In Rng
ReDim Preserve arr(i)
arr(i) = myCell
i = i + 1
Next myCell
ActiveSheet.Range("H10:H14") = arr()
End Sub
Here you can see that the values in the watch window are what has been loaded in
Except, when I add the array back to the workbook it only pastes back the first element of the array.
Is it possible to paste the whole array to the worksheet without having to loop through the array?
After taking a look at the link from Sorceri, I have amended to code to use the .Transpose function, so my amended code now look like this:
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") = WorksheetFunction.Transpose(arr)
End Sub
Right-click the array shell border and select Data Operations»Paste Data from the shortcut menu to paste data.
Copy the table below and paste it into Excel in cell A1. Be sure to select cells E2:E11, enter the formula =C2:C11*D2:D11, and then press Ctrl+Shift+Enter to make it an array formula. In the sample workbook, select cells E2 through E11. These cells will contain your results.
To Fill a Dynamic Array On the Tools menu, point to Macro and then click Macros. In the Macro dialog box, click fill_array, and then click Run.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With