How to Fill List box on Excel User-Form with Unique Values from Worksheet

Good evening!

I am looking for VBA code to for Filling a Listbox on Excel User-Form with Unique Values from Worksheet.

I have string values in a column. Some of them are repeats. I just want unique values as the source for the combo box please.

Thank you Raghu

Expert Asked on July 12, 2017 in VBA: User Forms.
Add Comment
4 Answer(s)

Hi,

We can follo several methods to fill the unique values from the worksheet to ListBox.

Here is my preferred method to add unique values from worksheet to list box. This example macro will read the data from active worksheet from column A and Fill the unique strings into list box. You can use this code for both List box and Combo Box.

VBA to Fill List box on Excel User-Form with Unique Values from Worksheet

Private Sub UserForm_Initialize()
ListBox1.Clear
strCol = "A" 'Column number
lRow = 10 'Last Row with Data
For iCntr = 1 To lRow
nLookUp = Application.WorksheetFunction.CountIf(Range(strCol & "1:" & strCol & iCntr), Range(strCol & iCntr))
'Add Only First Occurance
If nLookUp = 1 Then ListBox1.AddItem Range(strCol & iCntr)
Next
End Sub

Thanks!

Keymaster Answered on August 6, 2017.
Add Comment

Thanks PN…

Expert Answered on August 8, 2017.
Add Comment

Thanks a lot PNRao for this great solution

Another approach using collection

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim myList As Collection
Dim myRange As Range
Dim myCell As Range
Dim myVal As Variant
Set ws = ThisWorkbook.Sheets("Sheet1")
Set myRange = ws.Range("A1", ws.Range("A1").End(xlDown))
Set myList = New Collection
With Me.ListBox1
On Error Resume Next
For Each myCell In myRange.Cells
myList.Add myCell.Value, CStr(myCell.Value)
Next myCell
On Error GoTo 0
For Each myVal In myList
.AddItem myVal
Next myVal
End With
End Sub
Expert Answered on August 11, 2017.
Add Comment

Another approach using dictionary

Private Sub UserForm_Initialize()
Dim arr As Variant
arr = GetDistinct(Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row))
ListBox1.List = arr
End Sub
Function GetDistinct(ByVal oTarget As Range) As Variant
Dim vArr As Variant
Dim v As Variant
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
vArr = oTarget
For Each v In vArr
dic(v) = v
Next v
GetDistinct = dic.Items()
End Function
Expert Answered on August 11, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.