API reference · Arrays

DeleteElement

Remove one row or column from a 2D array and compact the remaining values.

FunctionArrays→VariantSource L759

Signature#

Function DeleteElement(ByVal vArr As Variant, ByVal lIndexDel As Long, ByVal eDeletionType As DeletionType) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Allocated 2D array from which to remove an entire row or column.
`lIndexDel`	`Long`ByVal	Required	Actual row or column subscript to remove. An index outside the selected dimension returns the original array unchanged.
`eDeletionType`	`DeletionType`ByVal	Required	DeletionType.eDeleteLine=1 removes a row; DeletionType.eDeleteColumn=2 removes a column. This is the reverse of SelectionType's corresponding numeric mapping.

Returns#

Variant containing a new 2D array with the selected upper bound reduced by one. Both lower bounds and the other dimension's bounds are preserved. Removing the sole remaining row or column returns Empty. Invalid rank or deletion type also returns Empty.

Example#

SEE THE TRANSFORMATIONDelete row 2

Before

transactions2D · 3 × 2

Rows 1..3 · Columns 1..2

row / col	1	2
1	“Sales”	100
2	“Duplicate”	100
3	“Fees”	12

After

result2D · 2 × 2

Rows 1..2 · Columns 1..2

row / col	1	2
1	“Sales”	100
2	“Fees”	12

The remaining Fees row shifts from index 3 to index 2.

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

Sub ExampleDeleteElement()
    Dim a As New Arrays
    Dim transactions As Variant, result As Variant

    ReDim transactions(1 To 3, 1 To 2)
    transactions(1, 1) = "Sales": transactions(1, 2) = 100
    transactions(2, 1) = "Duplicate": transactions(2, 2) = 100
    transactions(3, 1) = "Fees": transactions(3, 2) = 12

    ' DeletionType.eDeleteLine = 1
    result = a.DeleteElement(transactions, 2, 1)
    Debug.Print UBound(result, 1) ' Expected: 2
    Debug.Print result(2, 1)     ' Expected: Fees
End Sub

Behavior and edge cases#

Only true 2D arrays are accepted. A 1D vector is not expanded automatically.
Values after the removed position shift back by one subscript. Relative row or column order is preserved.
An out-of-range deletion index is a no-op that returns the original array, whereas an unsupported eDeletionType returns Empty.
Deleting the only row or only column returns Empty rather than a zero-sized matrix. Check IsArray before reading or writing the result.
Values, including Empty, Null and errors in retained cells, are copied without conversion. The input array is not modified.

Performance#

O(r × c) time in the matrix size and output storage proportional to the retained matrix. The function performs one output allocation and one nested copy loop.

View implementation

Function DeleteElement(ByVal vArr As Variant, ByVal lIndexDel As Long, ByVal eDeletionType As DeletionType) As Variant
    'Removes one row or column while preserving the other dimension and its bounds

    Dim vArr_Out                As Variant
    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim lRowSource              As Long
    Dim lColSource              As Long

    If Not IsArray(vArr) Then Exit Function
    If Me.DimCounter(vArr) <> 2 Then Exit Function

    Select Case eDeletionType
        Case eDeleteLine
            If lIndexDel < LBound(vArr, 1) Or lIndexDel > UBound(vArr, 1) Then
                DeleteElement = vArr
                Exit Function
            End If
            If LBound(vArr, 1) = UBound(vArr, 1) Then Exit Function
            ReDim vArr_Out(LBound(vArr, 1) To UBound(vArr, 1) - 1, LBound(vArr, 2) To UBound(vArr, 2))

        Case eDeleteColumn
            If lIndexDel < LBound(vArr, 2) Or lIndexDel > UBound(vArr, 2) Then
                DeleteElement = vArr
                Exit Function
            End If
            If LBound(vArr, 2) = UBound(vArr, 2) Then Exit Function
            ReDim vArr_Out(LBound(vArr, 1) To UBound(vArr, 1), LBound(vArr, 2) To UBound(vArr, 2) - 1)

        Case Else
            Exit Function
    End Select

    For lRow = LBound(vArr_Out, 1) To UBound(vArr_Out, 1)
        lRowSource = lRow
        If eDeletionType = eDeleteLine And lRow >= lIndexDel Then lRowSource = lRow + 1

        For lCol = LBound(vArr_Out, 2) To UBound(vArr_Out, 2)
            lColSource = lCol
            If eDeletionType = eDeleteColumn And lCol >= lIndexDel Then lColSource = lCol + 1
            vArr_Out(lRow, lCol) = vArr(lRowSource, lColSource)
        Next lCol
    Next lRow

    DeleteElement = vArr_Out
End Function