Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redimming an array dynamically assigned from range (how to redim f
Someone can correct me on this, but I thought I remember that redim is worse
than just "over-dimming". -- -John Please rate when your question is answered to help us and others know what is helpful. "Keith R" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order)
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 "Keith R" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redimming an array dynamically assigned from range (how to redimfirst dimension of a 2-D array? /or/ reverse the original array order)
Keith R wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
redim preserve the second dimension in a two-dim array | Excel Programming | |||
ReDim an array AFTER it's loaded into another array | Excel Programming | |||
how to redim more than one dimension in a multidimensional dynamic array? | Excel Programming | |||
Mutli-dimensional Array to Single-Dimension Array | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming |