I have an function that needs to have a dynamic array..

Sub Array Test
Dim myArray() as String
Dim RowData as Long
Dim Counter as Integer

RowData = 1

Do While Cells(RowData, 1).VAlue <> ""
Redim Preserve myArray(RowData, 9)

For Counter = 0 to 9

myArray(RowData, Counter) = Cells(RowData, Counter + 1)

Next Counter
RowData = RowData + 1
Loop
End Sub

Excel doesn't let this work as you can only change the second part of the array when you preserve Redim it.

I see 3 possible solutions, but am unsure which one would be the best...

#1) Change the order so it would be [ReDim Preserve myArray(9, RowData)]

#2) Determine the max number that it could be before including the data, thus excluding it from needing the Preserve keyword.

#3) Create 2 similar arrays and swap the data between the two as you Redim to make them larger, but without the necessity of the Preserve.

But as this is more of a side project I'm working on I thought I'd ask here, rather then try each out to see the time difference in them.

Any help would be appreciated. Thanks,
John