Deleting Elements in an Array if Element is a Certain value VBA

Question

I have a global array, prLst() that be can of variable length. It takes in numbers as strings "1" to Ubound(prLst). However, when the user enters "0", I want to delete that element from the list. I have the following code written to perform this:

count2 = 0
eachHdr = 1
totHead = UBound(prLst)

Do
    If prLst(eachHdr) = "0" Then
        prLst(eachHdr).Delete
        count2 = count2 + 1
    End If
    keepTrack = totHead - count2
    'MsgBox "prLst = " & prLst(eachHdr)
    eachHdr = eachHdr + 1
Loop Until eachHdr > keepTrack

This does not work. How do I efficiently delete elements in the array prLst if the element is "0"?

---

NOTE: This is part of a larger program, for which the description of can be found here: Sorting Groups of Rows Excel VBA Macro

Answer 1

An array is a structure with a certain size. You can use dynamic arrays in vba that you can shrink or grow using ReDim but you can't remove elements in the middle. It's not clear from your sample how your array functionally works or how you determine the index position (eachHdr) but you basically have 3 options

(A) Write a custom 'delete' function for your array like (untested)

Public Sub DeleteElementAt(Byval index As Integer, Byref prLst as Variant)
       Dim i As Integer
        
        ' Move all element back one position
        For i = index + 1 To UBound(prLst)
            prLst(i - 1) = prLst(i)
        Next
        
        ' Shrink the array by one, removing the last one
        ReDim Preserve prLst(LBound(prLst) To UBound(prLst) - 1)
End Sub

(B) Simply set a 'dummy' value as the value instead of actually deleting the element

If prLst(eachHdr) = "0" Then        
   prLst(eachHdr) = "n/a"
End If

(C) Stop using an array and change it into a VBA.Collection. A collection is a (unique)key/value pair structure where you can freely add or delete elements from

Dim prLst As New Collection