Excel VBA Dynamic array

Note that if you have an array

Dim Arr()
ReDim Arr(1 To 6, 1 To 6)

you can only change the last dimension eg

ReDim Preserve Arr(1 To 6, 1 To 5) 

but never the first one. Therefore what you ask for is not possible without loosing the data in the array.

The only workaround for that is to create a new array and move the data from the old array to the new one field by field.

Write 6x6 array values back to sheet as if redimmed to 5x5

As far as I understand your intention, you want to - save 2-dim array values to a range (referenced by a given top cell) and - to get rid of the upper boundary in both dimensions for reasons whatever.

If not added one by one, you might have gotten the original data from a sheet range as follows

Dim v                               ' provide for a variant array
v = Sheet1.Range("A2").Resize(6, 6) ' assign all data to 1-based 2-dim array v

Note the fully qualified range reference via a sheet's CodeName, assuming basic data in a sheet with VBE Tool Window (Name) of e.g. Sheet1.

Attempt 1 - resize the hosting target range to receive array v

This would be the easiest way to get the needed values into the sheet, but with the disadvantage that you did'nt change the related array likewise, e.g.

With Sheet2
    .Cells.Clear            ' emptying any content in target sheet
    .Range("A2").Resize(5, 5) = v   ' start cell e.g. in A2 (leaving room for title row)
End With

To overcome this, you might want to copy to a temporary sheet and return the resized values only

    With Sheet2
        .Cells.Clear            ' emptying any content in target sheet
        .Range("A2").Resize(5, 5) = v   ' blueprint the entire array via a filtering bottle neck
        v = .UsedRange          ' resize the array, too :-)
    End With    

Attempt 2 - redim the original array

In case you'd try to redimension the original array via ► ReDim or ReDim Preserve, PEH mentioned correctly, that it's only possible to change the last dimension, e.g.

ReDim Preserve Arr(1 To 6, 1 To 5) 

but never the first one.

TL;DR

The cited answer states as well:

The only workaround for that is to create a new array and move the data from the old array to the new one field by field.

Out of inquisitiveness I tried to find other workarounds.

[1] Using the ► Application.Index function

    v = Application.Index(v, Evaluate("row(1:5)"), Array(1, 2, 3, 4, 5))

or alternatively via procedure call RD (short for ReDim)

RD v, 5, 5
'...

Sub RD(Arr, UBnd1&, UBnd2&)
    Arr = Application.Index(Arr, Evaluate("row(1:" & UBnd1 & ")"), Application.Transpose(Evaluate("row(1:" & UBnd2 & ")")))
End Sub

C.f. the advanced features and pecularities of the Application.Index function at Insert new first column in array without loops

[2] Using a listbox'es .List property

An alternative would be - to redim an array in its 2nd dim, - assign it to a ListBox.List created on the fly, - remove last rows and assign .List property back to the array

Caveat: the .List property returns a zero-based 2-dim array instead 1-based items see return 0-based array from UsedRange value to reflect the method.

[3] Possible memory methods avoiding too much loops, e.g. via MemCpy or CopyMemory

►Would be interested in any feed back knowing a valid answer, could pose a new question if wanted.