ReDim Preserve array to start at index 3

Question

I populate an array with a range, and would like to have the same field and record numbers in the array as on the actual range.
In other words: if the array is derived from A5:D10, myArr(5,2) should refer to B5.

Dim myArr As Variant
myArr = sht.Range("A3:M" & LRow)

'Redesign array references
    'This works
    ReDim Preserve myArr(1 To LRow -2, 0 To 12)

    'This does not work
    ReDim Preserve myArr(3 To LRow, 1 To 13)

Subscript out of range

The error message above suggest I may try to go out of bounds with the indexes, but they're the same size as the working indexes. What am I doing wrong here?

Answer 1

In a multi dimensional array you can only redesign ReDim the last array dimension.

For example in a multi dimensional array you can do

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(1 To 10, 1 To 25)

but you cannot do

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(1 To 15, 1 To 20)

nor can you do

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(1 To 10, 5 To 20)

or

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(5 To 10, 1 To 20)

The documentation of the ReDim statement says:

"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.
…
Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error."

The only solution to get around this would be to define a new array with the new dimensions and shift the data into this new array using a loop. But then the idea of loading a range of values into an array at once like

myArr = sht.Range("A3:M" & LRow).Value

is completly useless. Actually in this case loading the not needed data of the first 2 rows into the array too should be faster than shifting the array with a loop.

Use

myArr = sht.Range("A1:M" & LRow).Value

and you can use myArr(5, 2) to refer to B5. Loading the additional 2 lines into the array too should not make a big difference.