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
Listbox category
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel categories
8 Responses to “Populate a list box with unique distinct values from a filtered Excel table [VBA]”
Leave a 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.
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.
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.
Thanks very much but i want to show two column1 and column 2. i do not how to show them, pls help me!
Hoang,
VBA code
Get the Excel *.xlsm file
Excel-table-List-boxv2.xlsm
Two columns
How can I display the item selected from the listbox in an excel cell?
Your interest on this regard is much appreciated
RHansen,
[…] Populate a list box with visible unique values from an excel table (vba) […]
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