VBA - Remove empty items from ListBox

Question

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

FaneDuru · Accepted Answer · 2020-08-09 13:42:49Z

2

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.

edited Aug 9, 2020 at 13:42

answered Aug 9, 2020 at 13:37

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Çağlar Utku Güler · Accepted Answer · 2020-08-09 13:12:51Z

1

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

answered Aug 9, 2020 at 13:12

Çağlar Utku Güler

336 bronze badges

Collectives™ on Stack Overflow

VBA - Remove empty items from ListBox

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related