A good way to do this is to first determine a maximum number of rows
for the listbox, a value that you can be sure you will never exceed.
Then, create your array in the normal manner, and once the array is
loaded, Redim Preserve it do the the actual used size. Since you can
redim only the last dimenion of an array, and for a listbox that is
the number of columns, not rows, you have to transpose your array when
loading the listbox.

' we can be sure the array will never exceed this size
Const MAX_SIZE As Long = 1000
Dim RealSize As Long
Dim Arr() As String

ReDim Arr(1 To 3, 1 To MAX_SIZE)
' populate the array with your values. note that
' these array indexes are by column then row, rather
' than row by column.
Arr(1, 1) = "r1 c1"
Arr(2, 1) = "r1 c2"
Arr(3, 1) = "r1 c3"
RealSize = RealSize + 1
Arr(1, 2) = "r2 c1"
Arr(2, 2) = "r2 c2"
Arr(3, 2) = "r2 c3"
RealSize = RealSize + 1
' and so on for the whole array

' shrink array to acutal size
ReDim Preserve Arr(1 To 3, 1 To RealSize)
With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "50;50;50"
' transpose the array to swap rows/columns
.List = Application.Transpose(Arr)
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com






On Sun, 7 Mar 2010 20:11:11 -0700, "Robert Crandal"
wrote:

I have a multi-dimensional array which is initially defined
as follows:

Dim MyArr() as String

My VBA code will then search all rows that contain data.
(BTW, each row contains 5 columns of data.)

If a row of data is found that matches the search criteria,
I want to place all that string data into the "MyArr" array.
So, if my search yields 230 rows of string data, I want my final
array size to be EXACTLY 230 rows by 5 columns, or:

ReDim MyArr (1 to 230, 1 to 5)

Can I redimension the array each time a search hit is
found and continuously add data to this array?? I guess I'm
really looking for a string array that grows each time a search
hit is found.

I know I could create an oversized array to begin with, but
in my case it is important that my array size be EXACTLY
the same number as the total number of rows that I will place
in the array. Anybody know what I can do???

Thank you!