Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 772
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 783
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
redim preserve the second dimension in a two-dim array Arnold Klapheck Excel Programming 4 September 19th 06 02:10 PM
ReDim an array AFTER it's loaded into another array Neal Zimm Excel Programming 11 August 21st 06 05:00 AM
how to redim more than one dimension in a multidimensional dynamic array? lvcha.gouqizi Excel Programming 8 October 28th 05 09:42 PM
Mutli-dimensional Array to Single-Dimension Array Blue Aardvark Excel Programming 3 October 15th 05 09:22 AM
ReDim Object array as parameter of Variant array Peter T Excel Programming 4 May 10th 05 02:11 PM


All times are GMT +1. The time now is 10:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"