Printable View
I'm struggling to figure out a way to convert a 2-dimensional array with horizontal data to a 1-dimensional array, or even just a single column of data. Basically, I need to take a data table that is laid out like a calendar going out several months and line it up in a column. Is it necessary to go with an array or is there an easier way to flip the numbers? Once I have a strategy, I think I will be OK.
After further digging, I think I can use the TRANSPOSE function to get what I need. The first page will take some work to complete, but I can cut and paste to the other worksheets and workbooks.
I'm learning!
Late but better than nothing I guess.
Code:Sub Convert2DTo1D()
Dim arr2D(1 To 2, 1 To 3) As Variant
Dim arr1D() As Variant
Dim i As Long, j As Long, k As Long
' Populate the 2D array
arr2D(1, 1) = 1
arr2D(1, 2) = 2
arr2D(1, 3) = 3
arr2D(2, 1) = 4
arr2D(2, 2) = 5
arr2D(2, 3) = 6
' Determine the size of the 1D array
ReDim arr1D(1 To UBound(arr2D, 1) * UBound(arr2D, 2))
' Populate the 1D
array k = 1
For i = 1 To UBound(arr2D, 1)
For j = 1 To UBound(arr2D, 2)
arr1D(k) = arr2D(i, j)
k = k + 1
Next j
Next i
' Print the 1D array to the Immediate Window
For i = 1 To UBound(arr1D)
Debug.Print arr1D(i)
Next i
End Sub
try:
=TOCOL()
?
Another way of trying your luck.
Code:Sub For_Many_If_You_Want()
Dim arr1, i As Long, j As Long
j = ActiveSheet.UsedRange.Columns.Count + 1
arr1 = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value '<---- Change the "B" to your last Column to be included
For i = LBound(arr1, 2) To UBound(arr1, 2)
Cells(Rows.Count, j).End(xlUp).Offset(1).Resize(UBound(arr1)) = Application.Index(arr1, , i)
Next i
End Sub