Excel VBA Insert New Row into an Array

Question

I have a 2D Array and I want to insert a number of rows in the middle of the array at some row number.

The Array already has existing information, so ReDim Preserve doesn't quite work if i wanted to simply place the new information in the middle.

Any ideas?

Answer 1

Assuming two 1-based 2-dim datafield inputs, I demonstrate an alternative to @FaneDuru's valid approach using a solitary combo list creation as data container and allowing a simple item insertion via the now disponible .AddItem method.

Note that of course this approach can be refined in many directions, e.g. by allowing different index bases for input arrays. - Methodical hint: Assigning the zero-based combo's .Column property (i.e. the inverted .List prop) and transposing it back allows here to regain the original one-based indexing of the input array as a ReDim Preserve would fail (it can only redim the last dimension).

Sub AddElement(datafield, ByVal rowNum As Long, newData)
'Note: assumes 1-based 2dim input arrays with same column counts
'create zero-based combo container on the fly
With CreateObject("Forms.ComboBox.1")
    .list = datafield     ' assign datafield to zero-based list elems
    .AddItem , rowNum - 1 ' add Null values to zero-based list index
    'assign new data to each zero-based list column
        Dim col As Long
        For col = LBound(newData, 2) To UBound(newData, 2)
            .list(rowNum - 1, col - 1) = newData(1, col)
        Next col
    'overwrite original datafield with transposed list values to regain 1-based elems
    datafield = Application.Transpose(.Column)    ' overwriting data (with zerobased dimensions)
End With

End Sub

As the datafield argument is passed implicitly by reference (ByRef), the original data input will be overwritten.

Example Call with insertion of a new 2nd row

Dim data:    data = Sheet1.Range("A1:C4").Value
Dim newData: newData = Sheet2.Range("A1:C1").Value
AddElement data, 2, newData