Yet another topic with multidimensional array and Redim Preserve, I know. I read a lot of them but still can not understand why my code is not working.
I kwow that you can only extend the last dimension and it is what I want: add a new column to my 2D array.
In order to isolate the issue, I test 2 code:
Sub test_Redim_Preserve()
Dim arr() As Variant
ReDim arr(10, 10)
ReDim Preserve arr(UBound(arr,1), UBound(arr,2) + 1)
End Sub
This work fine
Sub test_Redim_Preserve2()
Dim arr() As Variant
ReDim arr(10, 10)
arr = Range("A1:J10")
ReDim Preserve arr(UBound(arr,1), UBound(arr,2) + 1)
End Sub
This give me an error. I just gave a range to populate my array and then I can´t Redim it. I don´t understand what is missing for it to accept the Redim.
Could someone explain me?
The default lower bound, in the absence of an Option Base statement to the contrary, is 0 but you assign a range to an array, it always has a lower bound of 1, so your code is actually trying to resize the first dimension of the array too by altering its lower bound. Use:
ReDim Preserve arr(1 To UBound(arr, 1), 1 To UBound(arr, 2) + 1)