VBA - Remove empty items from ListBox

user_2812950 0
Viewed 798

I want to remove empty items from the ListBox I have created with array. However at some point, as each time the ListCount value is updated, the code fails. How can I overcome this problem?

Private Sub RemoveEmptyRows(lst As msforms.ListBox)
With lst
    For i = 0 To .ListCount - 1
        If .List(i) = False Then
            .RemoveItem i
        End If
    Next
End With

End Sub

listboxitem vba listbox
2 Answers

Try the next code, please:

Private Sub RemoveEmptyRows(lst As msforms.ListBox)
    Dim i As Long
    With lst
        For i = .ListCount - 1 To 0 Step -1
            If .List(i) = Empty Then
                .RemoveItem i
            End If
        Next
    End With
End Sub

The iteration must be done backwards. Otherwise, after items removing, their reference is lost...

That's why your solution, even not very efficient, works. After each item removal the iteration is restarted with new references for all existing items.

user_22333080

After some debugging I have found a solution. I hope that helps for others. Each time I remove an item, as the ListCount value is updated, I make it go back to the beginning. Not good for performance but it is working.

Private Sub RemoveEmptyRows(lst As msforms.ListBox)
    With lst
iterate:
        For i = 0 To .ListCount - 1
            If .List(i) = False Then
                .RemoveItem i
                GoTo iterate
            End If
        Next
    End With
End Sub
user_28129500
Related