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

Answer 2

In replay to @T.M. nice approach:

Try using the next function, which does not need transposing of column array:

Function AddElement(datafield, ByVal rowNum As Long, newData) As Variant
    '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
        AddElement = .list 'it returns a zero based 2D array...
    End With
End Function

It can be tested in this way:

Sub testAddArrayrow()
 Dim sh As Worksheet, newArr: sh = ActiveSheet  'was easier for me to check...
 Dim data:    data = sh.Range("A1:C4").Value
 Dim newData: newData = sh.Range("A6:C6").Value
    newArr = AddElement(data, 2, newData)
    sh.Range("P1").Resize(UBound(newArr) + 1, UBound(newArr, 2) + 1).Value = newArr '+ 1 because of zero bazed 2D returned array
End Sub

We must make some researches in order to check the array size limitations, if any. It will be very interesting to not have such limitations, but I simply cannot hope too much from this direction... :)

Anyhow, the brilliant idea remains...

Answer 3

Please, try the next way:

Sub TestInsertRow()
    Dim sh, arr, arrInsR, arrFin
    
    Set sh = ActiveSheet
    arr = sh.[A1:C5].Value       'array to insert a row
    arrInsR = sh.[A11:C11].Value 'row to be inserted
    arrFin = InsertRowElab(arr, arrInsR, 3) 'inserted row to be the third
   sh.[J10].Resize(UBound(arrFin), UBound(arrFin, 2)).Value = arrFin
End Sub

Private Function InsertRowElab(arr As Variant, arrIns As Variant, insRowNo As Long) As Variant 'it inserts a row
   Dim i&, k&, Ar, arrR, arrRows, arrCols
   
   arrR = Application.Transpose(Application.Evaluate("row(1:" & UBound(arr) & ")")) 'build the rows array
    Debug.Print "Joined rows array: " & Join(arrR, "#"):                                        'so the joined array looks
   arrRows = Split(Replace(Join(arrR, "|"), insRowNo - 1 & "|", insRowNo - 1 & "|0|"), "|")      'insert the row, placing 0 in the array
    Debug.Print "Joined rows array containing the inserted row (0): " & Join(arrRows, "#")       'so the joined array containing the new row (0) looks
   arrCols = Application.Transpose(Application.Evaluate("row(1:" & UBound(arr, 2) & ")")) 'Array(1, 2, 3)
   Ar = Application.Index(arr, Application.Transpose(arrRows), arrCols)
   'fill the inserted row:
   For i = LBound(Ar) To UBound(Ar, 2): Ar(insRowNo, i) = arrIns(1, i): Next i
   InsertRowElab = Ar
End Function