Working with 2d Array

[bjorgen]

I have a 2 dimensional array (necessary for earlier in the code) and want to output all of the values into a column in excel.

As for the location I want it to be one to the right of a named column range. Having had previous array values in the other columns.

Is there any way to output all of the values into a column from a 2d array or do I need to turn it into a 1d array first?

IE, in a worksheet-
Header-----------Header 2
arrayItem(0,0)--arrayitem(0,0)
arrayItem(0,1)--arrayItem(1,0)
arrayItem(1,0)--arrayItem(1,1)

 

[GTO]

Are you wanting to fill a range the same size/shape as the array?

 

[bjorgen]

Are you wanting to fill a range the same size/shape as the array?

Yes, I am trying to fill a column with all of the values in the 2d array.

The dimensions of the array are 0 - 8 for the first and varying for the second.

 

[GTO]

Okay, but to be clear, you indicate your array as having multiple/unknown number of 'columns'. Are we filling a range nine rows by x columns? Or are we "trying to fill a column" (as in one column) with all the elements?

 

[bjorgen]

Yes, I am basically discarding the 2d aspect at this point in the code and dropping everything into one column.

If you have a solution to both (ie dropping all in one column and pasting as a 2d array, across columns) I'd love to hear them.

 

[GTO]

Here is both ways:

Option Explicit
    
Sub exa()
Dim Ary()
Dim x As Long, y As Long, i As Long
Dim aryOutput()
    
    '// randomly size and fill example array    //
    Randomize
    ReDim Ary(0 To 9, 1 To Int((50 - 25 + 1) * Rnd() + 25))
    
    For x = LBound(Ary, 1) To UBound(Ary, 1)
        For y = LBound(Ary, 2) To UBound(Ary, 2)
            Ary(x, y) = x + y
        Next
    Next
    
    
    '// Size an output array to later fill one column   //
    ReDim aryOutput(1 To (UBound(Ary, 1) - LBound(Ary, 1) + 1) * (UBound(Ary, 2) - LBound(Ary, 2) + 1), 1 To 1)
    
    '// Use L/UBound.  IMO, much better than counting on a certain base.    //
    '// Loop the 2D w/ink col count into 2D w/one col   //
    i = 0
    For x = LBound(Ary, 1) To UBound(Ary, 1)
        For y = LBound(Ary, 2) To UBound(Ary, 2)
            i = i + 1
            aryOutput(i, 1) = Ary(x, y)
        Next
    Next
    '// Plunk wherever  //
    Range("A1").Resize(UBound(aryOutput, 1)).Value = aryOutput
    
    '// Or, skip looping and just plunk orig array  //
    Range("C1").Resize(UBound(Ary, 1) - LBound(Ary, 1) + 1, UBound(Ary, 2) - LBound(Ary, 2) + 1).Value = Ary
    
End Sub

 

[bjorgen]

Here is both ways:

Option Explicit
    
Sub exa()
Dim Ary()
Dim x As Long, y As Long, i As Long
Dim aryOutput()
    
    '// randomly size and fill example array    //
    Randomize
    ReDim Ary(0 To 9, 1 To Int((50 - 25 + 1) * Rnd() + 25))
    
    For x = LBound(Ary, 1) To UBound(Ary, 1)
        For y = LBound(Ary, 2) To UBound(Ary, 2)
            Ary(x, y) = x + y
        Next
    Next
    
    
    '// Size an output array to later fill one column   //
    ReDim aryOutput(1 To (UBound(Ary, 1) - LBound(Ary, 1) + 1) * (UBound(Ary, 2) - LBound(Ary, 2) + 1), 1 To 1)
    
    '// Use L/UBound.  IMO, much better than counting on a certain base.    //
    '// Loop the 2D w/ink col count into 2D w/one col   //
    i = 0
    For x = LBound(Ary, 1) To UBound(Ary, 1)
        For y = LBound(Ary, 2) To UBound(Ary, 2)
            i = i + 1
            aryOutput(i, 1) = Ary(x, y)
        Next
    Next
    '// Plunk wherever  //
    Range("A1").Resize(UBound(aryOutput, 1)).Value = aryOutput
    
    '// Or, skip looping and just plunk orig array  //
    Range("C1").Resize(UBound(Ary, 1) - LBound(Ary, 1) + 1, UBound(Ary, 2) - LBound(Ary, 2) + 1).Value = Ary
    
End Sub

This works great!

Is there any way to not include any blank items in the output? That's the only problem i have left.

 

[bjorgen]

This works great!

Is there any way to not include any blank items in the output? That's the only problem i have left.

I added in "If ary(x,y) <> "" Then around the adding to the array bit.

That solved it, thanks so much for your help!

 

[GTO]

Thank you for the feedback and you are most welcome