Clearing blank results from a Listbox using VBA

In this example, i have a Userform with a Listbox (Listbox1) and a Button (CommandButton1).

When a Listbox is populated, you might want to remove empty entries with the click of a button. The following code will remove those entries.

Create a User form as per above. 1 List box called Listbox1 and a Button called CommandButton1.

Within the User form, paste the following code.

Option Explicit

'This button will remove the blank entries.

Private Sub CommandButton1_Click()

  Dim l As MSForms.ListBox: Set l = Me.ListBox1

  Dim i As Long: i = 0

  '' remove anything that looks like a blank

  While i < l.ListCount

    If "" = Trim$(l.List(i, 0)) Then: l.RemoveItem (i): Else i = 1 + i

  Wend

End Sub

'For demonstration purposes, when the Userform is activated it will populate the

'Userform with 8 items along with blank entries.

Private Sub UserForm_Activate()

  ' locals

  Dim l As MSForms.ListBox: Set l = Me.ListBox1

  Dim i As Long: i = 0

  ' stick items in the list box

  For i = 1 To 8

    Call l.AddItem("Item : " & CStr(i))

    Call l.AddItem("    ")

  Next i

End Sub