Excel VBA Arrays: Is there a simple way to delete a dataset by the index?

Question

Is there a simple method to remove a specific data set from an array by the index?

Example:

Dim array() as string

ReDim array(2,2)

array(0,0) = "abc"
array(0,1) = "Peter"
array(0,2) = "New York"

array(1,0) = "xyz"
array(1,1) = "Bob"
array(1,2) = "Los Angeles"

array(2,0) = "klm"                       ' edited (enumeration error in OP)
array(2,1) = "Stacey"
array(2,2) = "Seattle"

So my array shows at
0: abc, Peter,New York
1: xyz, Bob, Los Angeles
2: klm, Stacey, Seattle

I now know from a previous calculation I don't need Bob at index 1 anymore and I want to delete his record

Is there something simple like the following?

ReDim Preserve array(UBound(array) - 1)
array.delete(1)

Answer 1

try this for 1d arrays

Sub test()
Dim strr As String
strr = "0|1|2|3|5"
wArr = Split(strr, "|")
d = DeleteElementAt(2, strr)
End Sub


Function DeleteElementAt(ByVal index As Integer, ByRef prLsts, strDelimeter) As String
       Dim i As Integer
        Dim newLst
        ' Move all element back one position
        prLst = Split(prLsts, strDelimeter)
        If UBound(prLst) > 0 Then
            ReDim newLst(UBound(prLst) - 1)
            For i = 0 To UBound(prLst)
                If i <> index Then newLst(y) = prLst(i): y = y + 1
            Next
            DeleteElementAt = Join(newLst, strDelimeter)
        Else
            DeleteElementAt = prLsts
        End If

End Function

for 2D array

Function Delete2dElementAt(ByVal index As Integer, ByRef prLsts) As Variant
       Dim i As Integer
        Dim newLst
        ' Move all element back one position
        prLst = prLsts
        If index > UBound(prLst) Then MsgBox "overcome index": Exit Function
        If UBound(prLst) > 0 Then
            ReDim newLst(UBound(prLst) - 1, UBound(prLst, 2))
            For i = 0 To UBound(prLst)
                If i <> index Then
                    For Z = LBound(prLst, 2) To UBound(prLst, 2)
                        newLst(y, Z) = prLst(i, Z)
                    Next Z
                    y = y + 1
                End If
            Next
            Delete2dElementAt = newLst
        Else
            Delete2dElementAt = prLsts
        End If

End Function

Answer 2

Simple Procedure call close to your post, but returning a 1-based array

Using the advanced possibilities of the Application.Index function I demonstrate an approach close to your wanted pseudo-method array.delete(1) in OP:

  delArr arr, 2            ' see section [1] in the Example call below

where

• delArr is the calling procedure,
• arr is the array name (don't use a reserved function's name to baptize your array!) and
• 2 is the element row number as Application.Index returns only 1-based arrays.

Example call

Sub DelGivenElementNumber()
  Dim arr() As Variant, i As Long
  ReDim arr(1 To 3, 1 To 3)                 ' redimension to 1-based array :-)
  arr(1, 1) = "abc": arr(1, 2) = "Peter": arr(1, 3) = "New York"
  arr(2, 1) = "xyz": arr(2, 2) = "Bob": arr(2, 3) = "Los Angeles"
  arr(3, 1) = "klm": arr(3, 2) = "Stacey": arr(3, 3) = "Seattle"

' --------------------------------
' [1] delete element row  number 2          ' i.e. xyz|Bob|Los Angeles
' --------------------------------
  delArr arr, 2

' [2] optionally: check remaining element rows (now 1-based!)
  For i = LBound(arr) To UBound(arr)
      Debug.Print i, dispArrElements(arr, i)
  Next i
End Sub

Result of the restructured 1-based array in the VBE's immediate window:

1   abc, Peter, New York
2   klm , Stacey, Seattle

Main procedure delArr

The main procedure delArr is only a one liner and has only two arguments:

1. the data array itself passed by reference and
2. the 'row' number to be deleted (1-based, e.g. 2 represents the second element row):

Sub delArr(arr, r As Long)
    arr = Application.Index(arr, validRows(arr, r), allCols(UBound(arr, 2)))
End Sub

Helper functions

The main procedure uses two helper functions to get arrays with the remaining row numbers and column numbers (here: Array(1,2,3) for e.g. three columns).

Function validRows(arr, ByVal n&) As Variant()
' Purpose: get 0-based 1-dim Array(1,3), i.e. all remaining original row numbers counting from 1, omitting 2
  Dim i&, nRows&
  nRows = UBound(arr) - LBound(arr) + 1                           ' original row number
  ReDim tmp(0 To nRows - 2)                                       ' zero-based tmp counter: -1, omitting element n: -1 ~~> -2
  For i = 1 To n - 1                                              ' collect elements before element n
      tmp(i - 1) = i
  Next i
  For i = n To nRows - 1                                          ' collect elements after element n
      tmp(i - 1) = i + 1                                          ' count old row numbers, but with reduced tmp counter
  Next i
'  Debug.Print Join(tmp, "|")
  validRows = Application.Transpose(tmp)                          ' return array of found row numbers
End Function

Function allCols(ByVal n&) As Variant()
' Purpose: get 0-based 1-dim Array(1,2,... n), i.e. all column numbers
  allCols = Application.Transpose(Evaluate("row(1:" & n & ")"))
End Function

Optional function to display results

Uses the Join function to display one row element with all three columns of your example (see section [2] in the example call):

Function dispArrElements(arr, r As Long) As String
  dispArrElements = Join(Application.Transpose(Application.Transpose(Application.Index(arr, r, 0))), ", ")
End Function