Dealing with unknown array sizes
Ossie,
I just got to thinking, maybe there is a much easier solution
to my problem....
I am using a multi-dimensional string array because it seems
to be the easiest way to populate a multi-column listbox
control with string data. If I have an array of strings, I can
populate the listbox control by using the following code:
Userform1.Listbox1.List = MyArray
The first dimension of the array seems to control how many
rows will be displayed in the listbox. So, if the 1st dimension
of the array is set at 600, but the array only contains 2 rows
of data, that means the listbox will contain 600 rows: 2 rows
will contain data, but there will be 598 blank rows in the listbox
(which is unacceptable)!
So, do you know if I can somehow tell the listbox control to
only create N rows or items for the first N rows of the
array that actually contain data?? Maybe this would be easier?
BTW, thank you for taking the time write that great code below.
It really is awesome, but I'm just wondering if it might be
overkill.
Thank you!
Robert
"OssieMac" wrote in message
...
Hi again Robert,
Sub ReDimPreserveExample()
'Example of ReDim Preserve.
'Only last dimension can be ReDim Preserve
Dim myArr()
Dim C As Long 'Cols and 1st dimension in array
Dim R As Long 'Rows and 2nd dimension in array
With Sheets("Sheet1")
For C = 1 To 5
For R = 1 To 30
'Only ReDim on first loop of C
If C = 1 Then
ReDim Preserve myArr(1 To 5, 1 To R)
End If
myArr(C, R) = .Cells(R, C)
Next R
Next C
End With
MsgBox "# Elements in 1st dimension: " _
& UBound(myArr, 1) & vbLf & _
"# Elements in 2nd dimension: " _
& UBound(myArr, 2)
With Sheets("Sheet2")
For C = 1 To UBound(myArr, 1)
For R = 1 To UBound(myArr, 2)
.Cells(R, C) = myArr(C, R)
Next R
Next C
End With
End Sub
|