Clever Geek Handbook

VBA: Reduce loop iterations after deleting an array element?

In VBA for Excel:

For i = 0 To UBound(artMaster)
    For j = i To UBound(artMaster)
        If i <> j And artMaster(i).VDN = artMaster(j).VDN Then
            Call DeleteArrayItem(artMaster, j)
        End If
    Next j
Next i

How can I reduce the iterations of the loop after I have removed one of the array elements?

+3


source to share


2 answers


You will be much better off using WHILE loops instead of FOR loops. Alternatively, you can store UBound (artmaster) in a variable.



Dim I As Integer
Dim j As Integer
Dim n as Integer

i = 0
n = UBound(artMaster)

Do While i <= n
    j = i + 1

    Do While j <= n
        If artMaster(i).VDN = artMaster(j).VDN Then
            Call DeleteArrayItem(artMaster, j)
            n = n - 1
        End If

        j = j + 1
    Loop

    i = i + 1
Loop
+4


source


You can subtract 1 from your iterator. But this can be problematic and makes the code difficult to understand.



Perhaps the best approach is to loop from the last item to the first (step -1). This way, your iterator remains in effect when you remove items.

+9


source






More articles:


All Articles