UserForm ComboBox List – Need to Sort & Remove Duplicates

[grady121]

Hi to all.
I have a UserForm ComboBox that uses a list of golf venues on my worksheet in range : Range("C5:C" & Lastrow).Value

This works OK, but can anyone offer a way to Sort them in alphabetically order and remove any duplicates?

I’ve tried to apply a few of the codes I’ve found online, but don’t have the skills to adapt them. All I have now is the really basic first lines.

I only have the one sheet, named “Scores” and the UserForm is named “MyScores” if this is required.

Private Sub UserForm_Initialize()


Dim Lastrow As Long


Lastrow = Cells(Rows.Count, "C").End(xlUp).Row

ComboBox1.List = Range("C5:C" & Lastrow).Value



End Sub

Thanks for looking in.

 

[Micron]

Would it not be simpler, even if you had to add a sheet, to have a helper column of course names and sort that? I realize that won't prevent dupes but when sorted, they'll be easy to spot. This would also allow you to spot dupes that are misspelled because if you had Glen Oakes and Glen Oaks they are not duplicate values, but would be dupes of a course which would be easy to remove. Then you'd base your combo list on that column.

 

[Fuji]

One way

Private Sub UserForm_Initialize()
    Dim wsName$, s1$, s2$, x
    wsName = "sheet1"
    s1 = "Select Distinct F1 From `" & wsName & "$c5:c` Where F1 Is Not Null Order By F1;"
    s2 = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=No';"
    With CreateObject("ADODB.Recordset")
        .Open s1, s2, 3, 3, 1
        If .RecordCount Then Me.ComboBox1.Column = .GetRows
    End With
End Sub

 

[grady121]

Thanks for the help/advice guys.
Following the help/advice from Micron, I spent a little time building my own code, step by step so that I could understand it as I went along.
It may seem a little basic/longer than some of I've seen, but I seem to have got there in the end. P.S. I'm just using Column N as a donor for the list.
From the Userform Initialize, I just call the following Sub:

Sub Venues()
    
    'Copy existing Venues into column N
    Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row).Copy
    Range("N5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ' Remove duplicate Venues from list
    With ActiveSheet
        LastroW = .Cells(.Rows.Count, "N").End(xlUp).Row
        .Range("N5:N" & LastroW).RemoveDuplicates Columns:=1, Header:=xlNo
    End With
    
    ' Sort list Alphabetically
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("N5:N200") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("N5:N200")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Application.GoTo Cells(Rows.Count, "B").End(xlUp).Offset(1)

End Sub

Once again, thanks for looking in.