From Array into Array to Range

Question

I Created a TempTable as ReDim TempTable(1 To RowSize, 1 To 3), for each cell I've a vector of 55 value. See image below to better understand

Now I'd like to copy the entire content of TempTable in my worksheet. The range should be [7576x55, 3] since TempTable has 7576 rows. Is there any smart and fast way to do that?

Create a new 2D array of the appropriate size; then loop through your original array and populate it. Possibly DIM V(1 to 7576, 1 to 3*55). Then set R = R(7576,165) and R.Value = V — Ron Rosenfeld
– Ron Rosenfeld, Commented Dec 17, 2015 at 13:36
Dim tmpTBL as variant: tmpTBL = Range("A1:BC7576").Value2: Debug.Print LBound(tmpTBL, 1) & ":" & UBound(tmpTBL, 1): Debug.Print LBound(tmpTBL, 2) & ":" & UBound(tmpTBL, 2). I don't understand what you want to do with the 55 (... or 3?) values. — user4039065
– user4039065, Commented Dec 17, 2015 at 13:44
@Jeeped I need to paste them into a worksheet. Ideally I need to take TempTable(1,1) copy to A1:A55, then tahe TempTable(1,2) and copy to B1:B5, then TempTable(1,3) to C1:C55. Now shift down and stack so, TempTable(2,1) to A56:A111, TempTable(2,2) to B56:B111, etc. — gmeroni
– gmeroni, Commented Dec 17, 2015 at 16:54
Both arrays and worksheets can handle 1,048,576 rows. Why the third dimension to the array? — user4039065
– user4039065, Commented Dec 17, 2015 at 16:59
@R3uK: I tried but wasn't getting the data in the way i liked. I ended up rewrinting the whole process and get a Temp Table of the right dimensions at the first population. See here the code: stackoverflow.com/a/34398976/2349516 — gmeroni
– gmeroni, Commented Dec 21, 2015 at 16:22

R3uK · Accepted Answer · 2015-12-17 17:04:56Z

1

This should work smoothly (if the vectors in each column have the same size) :

Sub test_gmeroni()
Dim TempTable() As Variant, _
    wS As Worksheet, _
    Rg As Range, _
    InBound As Long, _
    i As Long, _
    j As Long

Set wS = ThisWorkbook.Sheets("OutPut")
Set Rg = wS.Range("A1")
ReDim TempTable(1 To 7576, 1 To 3)

For i = LBound(TempTable, 1) To UBound(TempTable, 1)
    For j = LBound(TempTable, 2) To UBound(TempTable, 2)
        'Calculate vectors size
        InBound = UBound(TempTable(i, j), 1)
        'Put vector on sheet
        Rg.Offset(0, j - 1).Resize(InBound, 1).Value2 = TempTable(i, j)
    Next j
    'Select next cell to start printing next row
    Set Rg = Rg.Offset(InBound + 1, 0)
Next i

End Sub

answered Dec 17, 2015 at 17:04

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

From Array into Array to Range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related