Redim preserve does not work for me

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I am trying to understand hoe redim preserve works, but I keep getting run
time error 9

Any idea what is wrong with my redim preserve.
Thank you.
Dan

For example in the bellow:

Public MyReportArray() As Variant
Sub testpreserve()
ReDim MyReportArray(5, 5)
MyReportArray = Range("A5:E5")
ReDim Preserve MyReportArray(6, 6)
MyReportArray = Range("A6:F5")
End Sub
 
From Excel help file....

If you use Preserve, you can only resize the last array dimension and you
can't change the number of dimensions at all.
 
What Nigel has mentioned is

Dim MyReportArray
ReDim MyReportArray(5, 5)

You can ReDimension to MyReportArray(5,6) or to MyReportArray(5,100)
but cannot
ReDimension to MyReportArray(6,5)

If this post helps click Yes
 
If you declare array in tthis way: MyReportArray = Range("A6:F5") then you
don't need Redim at all.

Public MyReportArray() As Variant
Sub testpreserve()
MyReportArray = Range("A5:E5")
MyReportArray = Range("A6:F5")
End Sub

worked for me and MyReportArray contained cell contents A6:F5.

Regards,
Stefi


„Dan†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array range error question 1
Redimming 2D array to 1D while preserving the data? 8
Redim Preserve doesn't work 3
Scattered array cells copy to scattered array cells another workbook 14
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or 3
How to write Property Let for the single elements of an array 4
ReDim Preserve on 2D Array not working 5
Array Help 2

Back
Top