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

Related posts:

Excel vba: Populate a combobox with values from a pivot table

Excel vba: Populate a combo box (form control)

Vlookup visible data in a table and return multiple values in excel

Count unique and duplicates text values in a closed workbook in excel (formula)

Extract unique distinct values from a filtered table (udf and array formula)