Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or

[Keith R]

Per XL2003's Redim help: If you use the Preserve keyword, you can resize
only the last array dimension and you can't change the number of dimensions
at all.

I declared/filled my arrays by assigning a range to my array variant, which
organizes my array as [1 to X rows, 1 to Y columns]. However, I need to add
additional rows while my code is running, and Excel automatically makes that
the first dimension- so apparently I can't Redim it?

Is there a way to either force Excel to assign the range in the reverse
order to my array [1 to Y columns, 1 to X rows] so I can use the redim (and
re-write all of my code to match), or is there a better way to do this? I'll
need to paste the array back to a worksheet range when the macro ends, in
case that affects which solution I should use.

Thank you,
Keith

Option Base 1
Public ExpediteArray As Variant '(1 To x rows, 1 To 40 columns)

Sub MyStuff
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArray = Sheet4.Range("A1:AN" & CStr(Expedite_LastRow)).Value

'then later
ReDim Preserve ExpediteArray(LBound(ExpediteArray, 1) To
UBound(ExpediteArray, 1) + 1, LBound(ExpediteArray, 2) To
UBound(ExpediteArray, 2))


End sub

 

[Guest]

Someone can correct me on this, but I thought I remember that redim is worse
than just "over-dimming".

 

[Peter T]

Hi Keith,

I can't follow the values you are using to redim Preserve in your code but
understand you are hit by the fact you can only 'Preserve' the last
dimension, but in your 2D array you may want to redim preserve both rows and
columns.

The normal approach is to use the Transpose function. However I find that
quite slow and older versions are limited to just over 5k total elements.

What I do is start by Redim'ing an array with known absolute minimum number
of rows and maximum potential number of columns (memory is cheap), iow and
oversized array particularly the rows.

If during the process I need an extra last dimension (column) I use
Preserve.

When done, if dumping to cells I'd do something like this -

Range("A1").Resize(numRows,numCols).value = Arr

where Arr has been declared with L-base 1 and numRows & numCols represent
the filled or required portion of the array, ie numRows might be
considerably less than the first dimension's UBound.

If say you want to end up with a correctly sized array in both dimensions,
assign to a second appropriately sized array

Redim Arr(1 to 10000, 1 to 3)
'code
Redim Arr2(1 to 1234, 1 to 3)
Arr2 = Arr

Regards,
Peter T

 

[Alan Beban]

Per XL2003's Redim help: If you use the Preserve keyword, you can resize
only the last array dimension and you can't change the number of dimensions
at all.

I declared/filled my arrays by assigning a range to my array variant, which
organizes my array as [1 to X rows, 1 to Y columns]. However, I need to add
additional rows while my code is running, and Excel automatically makes that
the first dimension- so apparently I can't Redim it?

Is there a way to either force Excel to assign the range in the reverse
order to my array [1 to Y columns, 1 to X rows] so I can use the redim (and
re-write all of my code to match), or is there a better way to do this? I'll
need to paste the array back to a worksheet range when the macro ends, in
case that affects which solution I should use.

Thank you,
Keith

Option Base 1
Public ExpediteArray As Variant '(1 To x rows, 1 To 40 columns)

Sub MyStuff
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArray = Sheet4.Range("A1:AN" & CStr(Expedite_LastRow)).Value

'then later
ReDim Preserve ExpediteArray(LBound(ExpediteArray, 1) To
UBound(ExpediteArray, 1) + 1, LBound(ExpediteArray, 2) To
UBound(ExpediteArray, 2))


End sub

If the functions in the freelydownloadable file at
http://home.pacbell.net/beban are available to your workbook

ResizeArray ExpediteArray, , UBound(ExpediteArray, 1) + 1

Alan Beban