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