I have been searching the Internet and other resources for a practical example of how to solve a problem with two dimensional arrays. More particularly, assume MyArray() is to have two columns but the number of rows of data are yet unknown. The essence of the coding situation is:Option Base 1Dim MyArray() (variant assumed)Row_Num = 0Do Some_Thing While Cell_Check <> “”If Data_Value_X <> “” Or 0 thenRow_Num = Row_Num + 1ReDim Preserve MyArray(Row_Num, 2) ‘assumes two columns, variable rowsMyArray(Row_Num, 1) = Col_One_ValueMyArray(Row_Num, 2) = Col_Two_ValueEnd If. more code. more code. more code. incrementLoopWhat I have written above works only for the first resizing and doesn’t work for the second and following, getting an Error 9 (subscript out of range)However, I am using a single dimension array that employs the same syntax: i.e.Do While Some_Thing <> “”If Data_Value_X <> 0 Or “” thenRow_Num = Row_Num + 1ReDim Preserve MyArray(1 to N)MyArray(n) = Cell_ValueEnd If. more code. more code. more code. incrementLoopI fail to see the difference and I’ve not found an explanation in any of the resources that would explain why the strategy that works for a single dynamic array doesn’t work for a multi-dimensional array.Help! I need to resize a two column dynamic array.BigBill

Looks like a VB bug, though I suspect you worked that out yourself The ' X to Y' syntex is sticky, thus we need to cast it away it a different stack frame.I've written a tiny routine, which should do the job.See below, which works in VB6 and VBA in Excel 2000.At the risk of sounding too much like a CompSci graduate, you seem to be doing ragged arrays.There are a variety of other alternatives which may make for better code.Private Sub DomExample()Dim i As IntegerDim FirstOne()ReDim Preserve FirstOne(1 To 10)For i = 1 To 10 FirstOne(i) = iNext iReCast FirstOne, 2, 11For i = 2 To 11 FirstOne(i) = iNext iEnd SubSub ReCast(v As Variant, lower As Long, upper As Long)ReDim Preserve v(lower To upper)End Sub

Dominic:Thanks for your helpful reply. I'm using Excel 2000 9.0.The application fragment I'm writing involves processing credit invoices in an accounting system. Out of 100 transactions in a date range batch there are few credit transactions, therefore, I chose to add a row in the credit array with each occurrence, even though that is less efficient than adding row batches. That said, let me try your suggestion and I'll let you know what happens.Again, many thank for your help and interest in helping.BigBill

BigBill,You may want to try using another type of container, such as a collection object (a collection of arrays) - they're clean but slower than plain arrays.

Thanks for the comment. I have never used collections before. Will have to read up on them.BigBill

VBA does not allow the 1st dimension of a 2dim array to be changed dynamically.A way around it is to work on a transposed array structure.Hope this helps.Best regards,Gregory.

Thanks. I have read something in the past about transposing arrays but have not been able to find the source again. I thought there was a transpose instruction in VBA but haven't been able to find it. Ultimately, I solved my problem by resizing a two column array with an indefinite number of rows as Data(1,N) and Data(2,N) which VBA actually sees as a 2-row array with an indefinite number of columns. While the rows are really columns, it matters not as long as the programming treats the array consistently when filling and reading the data elements.

What you've done is exactly what I meant.You need to transpose the [2,N] Array only if you need to output its values in a [N,2] Range.The syntax could be, for example:ActiveSheet.Cells(1,1).Resize(N,2).Value = Excel.WorksheetFunction.Transpose(Data)Best regards,Gregory.

Thanks for the tip and the example of transpose.