• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Redim Preserve

Hi
I'm trying to under stand from the sites but this not working
any Ideas thanks


Code: 
Sub test_array()
    Dim test() As Variant
    Dim i As Integer
    test() = Range("i1:i4")
    For i = 1 To 3
   ReDim Preserve test(1 To 1, UBound(test()) To UBound(test()) + i)
      test(i) = "x" & i
      Range("b2") = UBound(test)
   
    Next i
    Range("a1:a" & UBound(test)) = test
End Sub
 
Last edited by a moderator:
When you assign a range to an array like that, you always get a 2 dimensional array. In your case it is sized (1 to 4, 1 to 1) so you are trying to resize the first and second dimensions, which you can't do when you use Preserve. You can only resize the last dimension.
Also, because you declared test as an array, not simply a Variant, you can only resize the upper boundary of the last dimension.
 
Thanks Mr. Debaser
Is there a way to resize the dimensions of the array (derived from range) using the keyword ReDim Preserve ..
Say I have values in Range("A1:A3") and we assigned an array to hold these values .. and at the code run time we need to extend the range to be A1:A5 ..in this case how could we use preserve to hold the values of the original array and add the new values to the array
 
YasserKhalil said:
Is there a way to resize the dimensions of the array (derived from range) using the keyword ReDim Preserve ..
Say I have values in Range("A1:A3") and we assigned an array to hold these values .. and at the code run time we need to extend the range to be A1:A5
Click to expand...

Assuming you can't just assign the new range to the array for some reason, you can transpose the range when assigning it so that the dimension you want to extend becomes the last dimension. You may also find it is faster to simply create a new array from the larger range and overwrite the values from the old array to the new array.
 
Debaser said:
Assuming you can't just assign the new range to the array for some reason, you can transpose the range when assigning it so that the dimension you want to extend becomes the last dimension. You may also find it is faster to simply create a new array from the larger range and overwrite the values from the old array to the new array.
Click to expand...
Hi Debaser
Does this mean that Redim Preserev is useless?
where and how you may use it?
thank you sir.
 
Not at all. You just need to ensure that your array is set up properly.

It is generally best to limit your use of it, though, since you are copying your entire array each time, which uses both memory and processing power. Typically when I do use it, I start by sizing the array larger than I will need and then use Redim Preserve once at the end to shrink it to the actual dimensions.
 

Hi !​
Debaser said:
I start by sizing the array larger than I will need and then use Redim Preserve once at the end to shrink it to the actual dimensions.
Click to expand...
If the purpose is to write the array to cells, no need to resize it …
​
 
Correct, and I wouldn't, but this was a general point about minimising its use when it is required. :)
 
You must log in or register to reply here.
Back
Top