Author: Oscar Cronquist Article last updated on January 31, 2019


Excel defined 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 distinct values from an Excel defined Table with a filter applied.

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

'Name macro
Sub FilterUniqueData()

'Declare variables and data types
 Dim Lrow As Long, test As New Collection, i As Single
 Dim Value As Variant, temp As Range

'Ignore errors in macro and continue with next line
On Error Resume Next

'Save values from first column in Table1 to temp object
Set temp = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Range

'Iterate through values in first column except header value
For i = 2 To temp.Cells.Rows.Count
    'Save value to test if the number of characters are more than 0 and the row is not hidden
    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

'Continue with next value
Next i

'Delete all items in listbox
Worksheets("Sheet1").Shapes("List Box 1").ControlFormat.RemoveAllItems

'Iterate through all values saved to test
For Each Value In test

   'Add value to listbox
   Worksheets("Sheet1").Shapes("List Box 1").ControlFormat.AddItem Value

'Continue with next value
Next Value

'Delete object test
Set test = Nothing

End Sub

Download Excel file


Excel-table-List-box.xlsm