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

1. 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
     

2. 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,

     

3. 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
4. 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 &lt; and > becomes &gt;

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