Populate a list box with unique distinct values from a filtered Excel table [VBA]

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

Sort or/and filter the table.
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

Get the Excel file

Excel-table-List-box.xlsm

Working with LIST BOXES (Form Controls)

This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]

Save invoice data [VBA]

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Working with COMBO BOXES [Form Controls]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Comments (8)

8 Responses to “Populate a list box with unique distinct values from a filtered Excel table [VBA]”

Glenda says:

May 16, 2013 at 4:06 pm

I have searched relentlessly for the answer to this question. How can I get the listbox to return the unique value but include the corresponding column 3 in your excample? I have a product list that has Kit and Lot number. I want the unique Kit but the correspoinding lot number for the kit with description.

Reply
- Oscar says:
  
  May 21, 2013 at 8:20 am
  
  Glenda,
  
  Form listboxes don´t support multi columns. Try an active x control.
  
  Google "vba multi column listboxes" and you'll find plenty of examples.
- Hoang says:
  
  June 27, 2013 at 6:28 pm
  
  Thanks very much but i want to show two column1 and column 2. i do not how to show them, pls help me!
- Oscar says:
  
  July 1, 2013 at 11:57 am
  
  Hoang,
  
  VBA code
```
Sub PopulatemulticolumnListbox()
Dim temp() As Variant
temp = Worksheets("Sheet2").Range("Table2").Value
With ActiveSheet.ListBox1
    .ColumnCount = 3
    .List = temp
End With
End Sub
```
  Get the Excel *.xlsm file
  Excel-table-List-boxv2.xlsm
  
  Two columns
```
Sub PopulatemulticolumnListbox()
Dim temp() As Variant
temp = Worksheets("Sheet2").Range("Table2[[Column1]:[Column2]]").Value
With ActiveSheet.ListBox1
    .ColumnCount = 2
    .List = temp
End With
End Sub
```
RHansen says:

September 24, 2013 at 12:04 pm

How can I display the item selected from the listbox in an excel cell?

Your interest on this regard is much appreciated

Reply
- Oscar says:
  
  September 27, 2013 at 1:10 pm
  
  RHansen,
Become more productive – Learn Excel Defined Tables says:

March 6, 2018 at 10:42 am

[…] Populate a list box with visible unique values from an excel table (vba) […]

Reply
Edoardo says:

June 14, 2018 at 9:38 am

Thanks Oscar for your precious work. I modified your code and adapted it to a combobox.

Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp As Range
Dim i As Single

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

UserForm1.ComboBox1 = Clear

For Each Value In test
UserForm1.ComboBox1.AddItem Value
Next Value

Set test = Nothing

Now, I would like to know how to sort those data filtered in the combobox. Thank you

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form