Excel tables, introduced in excel 2007, sort, filter and organize data any way you like. You can also format data and choose from many professional looking styles.

In this vba tutorial I will show you how to populate a list box with unique visible values from an excel table.

Instructions

  1. Sort or/and filter the table.
  2. Press "Populate list box" button.

Only unique visible values from Column1 are added to the list box.

VBA code

Sub FilterUniqueData()
    Dim Lrow As Long, test As New Collection, i As Single
    Dim Value As Variant, temp As Range

    On Error Resume Next
    Set temp = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Range

    For i = 2 To temp.Cells.Rows.Count
        If Len(temp.Cells(i)) > 0 And Not temp.Cells(i).EntireRow.Hidden Then
            test.Add temp.Cells(i), CStr(temp.Cells(i))
        End If
    Next i

    Worksheets("Sheet1").Shapes("List Box 1").ControlFormat.RemoveAllItems

    For Each Value In test
         Worksheets("Sheet1").Shapes("List Box 1").ControlFormat.AddItem Value
    Next Value

    Set test = Nothing

End Sub

Download excel 2007 MacroEnabled *.xlsm file

Excel table - List box.xlsm