Guides

Add and remove rows or columns

Edit array structure in memory before writing a result to Excel.

Append rows and columns#

InsertLines appends rows. InsertColumns appends columns. Neither accepts an insertion position. With bRedim=False, they overwrite trailing rows or columns without increasing the array size.

SEE THE TRANSFORMATIONAppend row → append column → delete row → delete column

Before

records2D · 2 × 2

Rows 0..1 · Columns 0..1

row / col	0	1
0	“TX-001”	120
1	“TX-002”	80

After

edited2D · 2 × 2

Rows 0..1 · Columns 0..1

row / col	0	1
0	“TX-001”	120
1	“TX-003”	200

Shape sequence: 2 × 2 → 3 × 2 → 3 × 3 → 2 × 3 → 2 × 2. The final shape matches the input, but TX-003 replaces TX-002.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub EditRecords()
    Const DELETE_ROW As Long = 1 ' DeletionType.eDeleteLine
    Const DELETE_COLUMN As Long = 2 ' DeletionType.eDeleteColumn
    Dim records As Variant, edited As Variant
    records = Arr.DimMerge(VBA.Array( _
        VBA.Array("TX-001", 120), VBA.Array("TX-002", 80)))
    edited = Arr.InsertLines(records, VBA.Array("TX-003", 200))
    ' A 1D array means one column when passed to InsertColumns.
    edited = Arr.InsertColumns(edited, VBA.Array("Posted", "Pending", "Posted"))
    edited = Arr.DeleteElement(edited, 1, DELETE_ROW) ' Remove TX-002
    edited = Arr.DeleteElement(edited, 2, DELETE_COLUMN) ' Remove Status
    Debug.Print edited(1, 0), edited(1, 1) ' Expected: TX-003, 200
End Sub

Overwrite the last row#

Sub OverwriteLastRow()
    Dim records As Variant, edited As Variant
    records = Arr.DimMerge(VBA.Array( _
        VBA.Array("TX-001", 120), VBA.Array("TX-002", 80)))
    edited = Arr.InsertLines(records, VBA.Array("TX-002", 90), False)
    Debug.Print edited(1, 1) ' Expected: 90; original remains 80
End Sub

Length and bounds#

New rows must have the same number of columns as the target. New columns must have the same number of rows. Source bounds may differ: the code aligns by relative position, not matching numeric indices. A 1D target passed to InsertLines is treated as one row, whereas DimExpand treats it as a column.

DeleteElement compacts the selected dimension, retaining its lower bound and decrementing its upper bound. An out-of-range deletion returns the original input. Deleting the only row or column returns Empty. Check IsArray before indexing a result.

For a middle insertion, this snapshot offers no dedicated insertion-index function. Build the desired arrangement explicitly using copying or slices; do not pass an index to InsertLines or InsertColumns.