Get Unique name list from name range-CodePudding

I have several names in column "H". I want to make a unique name list and show it in the Combobox dropdown list. Here is the code I have but it shows duplicate names in the list.

Sub Refresh_Customer_List()
Application.ScreenUpdating = False
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sale_Purchase")

Dim i As Integer

Me.cmb_Cust.Clear
Me.cmb_Cust.AddItem ""
If Application.WorksheetFunction.CountA(sh.Range("A:A")) > 1 Then
For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
Me.cmb_Cust.AddItem sh.Range("H" & i).Value
Next i
End If

End Sub

CodePudding user response：

Note how I create the range that might have your dropdown list elements in a few steps in the code below. We define the first row of interest (could be row 2) and then look for the last row with any data cells(rows.count,col).end(xlup)

If you have Office 365, you can populate the combolist without using a loop using something like:

Me.cmb_Cust.List = WorksheetFunction.Unique(Range(sh.Cells(2, "A"), sh.Cells(sh.Rows.Count, "A").End(xlUp)))

If you do not have the Unique function, there are many VBA objects you can use to obtain a distinct list. Here is one method using an ArrayList, but you can also use a Collection or Dictionary object.

For information on ArrayList, a currently valid link is VBA ArrayList - A complete guide

Dim AL As Object, v
Set AL = CreateObject("System.Collections.ArrayList")

For Each v In Range(sh.Cells(1, "A"), sh.Cells(sh.Rows.Count, "A").End(xlUp)).Value
    If Not AL.contains(v) Then AL.Add (v)
Next v

Me.cmb_Cust.List = AL.toarray