Transpose single column array

[mdmackillop]

I'm searching for files, returning the name, then opening them to get a value and a date. The information is returned to an array which is resized, transposed and shown in a 3 column listbox.
All is fine, unless only one file is found. In this case the 3 items of data appear in the first column of the listbox, whether I transpose the result or not. Any explanation?
The attached is a simplified example which demonstrates the issue.


Sub GetData1()
Dim f As Long
Dim arr()
Dim i As Long


ReDim arr(2, 200)
f = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 0 To f
arr(0, i) = Cells(1, i + 1)
arr(1, i) = Cells(2, i + 1)
arr(2, i) = Cells(3, i + 1)
Next
ReDim Preserve arr(2, f - 1)
ListBox1.List = Application.Transpose(arr)
Application.ScreenUpdating = False
End Sub

[mikerickson]

If arr is a 3 row X 1 column array, then Application.Transpose(arr) is a one dimensional array of 3 elements.

TryListBox1.Column = arr

[mdmackillop]

Thanks Mike. This works, but I can't get my head around the reasoning!
If f = 1 Then
ListBox1.Column = arr
Else
ListBox1.List = Application.Transpose(arr)
End If


With a bit more searching I found this

You can also use Column to copy an entire two-dimensional array of values to
a control. This syntax lets you quickly load a list of choices rather than
individually loading each element of the list using AddItem.

Note When copying data from a two-dimensional array, Column transposes the
contents of the array in the control so that the contents of
ListBox1.Column(X, Y) is the same as MyArray(Y, X). You can also use List to
copy an array without transposing it.

[mikerickson]

When a list box is filled with

ListBox1.List = arr

1) if arr is one dimensional, the elements will all be put in the first column of the list box.ListBox1.List = Array("able", "baker", "charlie")

2) if arr is two dimensional, the elements will be put in the list as a copy of the two dimensional array.

If arr is a 1 column X n row array, then Application.Transpose(arr) is one dimensional.
This demo code is for a 5 list box UF. Try changing arr to arr2 at the end.
Private Sub UserForm_Click()
Dim arr As Variant, arr2 As Variant, arrTest As Variant
ListBox1.ColumnCount = 3: ListBox2.ColumnCount = 3
ListBox3.ColumnCount = 3: ListBox4.ColumnCount = 3
ListBox5.ColumnCount = 3

ListBox1.List = Array("able", "baker", "charlie")

ReDim arr(0 To 2, 0 To 0)
arr(0, 0) = "able"
arr(1, 0) = "baker"
arr(2, 0) = "charlie"

ListBox2.List = arr
ListBox3.List = Application.Transpose(arr)

ReDim arr2(0 To 2, 0 To 1)
arr2(0, 0) = "2able"
arr2(1, 0) = "2baker"
arr2(2, 0) = "2charlie"
arr2(0, 1) = "2xable"
arr2(1, 1) = "2xbaker"
arr2(2, 1) = "2xcharlie"
ListBox4.List = arr2
ListBox5.List = Application.Transpose(arr2)

arrTest = Application.Transpose(arr)

On Error Resume Next
Me.Caption = UBound(arrTest, 2)
If Err <> 0 Then Me.Caption = Error
On Error GoTo 0
End Sub

[mdmackillop]

Thanks Mike.