VBA: Only add unique values to excel combobox, which is populated by looping through a source sheet range on workbook open

Question

The below code basically looks at a source sheet on workbook open, takes the values from a range and loops through adding each value to a combobox.

What I want to do is include some code to ensure only unique values, i.e. no dupes, are added.

Any ideas how I can get that working?

Thanks!

Private Sub Workbook_Open()

   Dim wb As Workbook
Set wb = ThisWorkbook

Dim Home As Worksheet
Dim Datasource As Worksheet


'Define Variables and dropdown object
Dim LastRow As Long
Dim MIDCell As Range


Dim ComboMID As ComboBox


Set Home = ActiveSheet
Set Home = Worksheets("UPDATER")
Set Datasource = wb.Sheets("LaunchCodes")


'asign dropdown object to combobox
Set ComboMID = Home.OLEObjects("ComboBox1").Object


'Empty the combobox currnetly to avoid duplicating content
ComboMID.Clear



'With and For loop to put all values in games launch code column, ignoring any blanks,  into combobox
With Datasource
      LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    For Each MIDCell In .Range("D2:D1000" & LastRow)
         If MIDCell.Value <> "" Then
            ComboMID.AddItem MIDCell.Value
           
    End If
    
Next
End With



End Sub

Answer 1

The code below avoids looping through cells in a worksheet because it's slow. Actually, that process can be sped up by reading the list into a variable (as, in fact, my code also does) but using Excel's own RemoveDuplicates method appears more efficient.

Private Sub Workbook_Open()
    ' 155

    Dim Wb          As Workbook
    Dim ComboMid    As ComboBox
    Dim TmpClm      As Long                 ' number of temporary column
    Dim Arr         As Variant              ' unique values from column D
    
    Set Wb = ThisWorkbook
    With Wb.Worksheets("UPDATER")
        Set ComboMid = .OLEObjects("ComboBox1").Object
        With .UsedRange
            TmpClm = .Column + .Columns.Count
        End With
    End With
    
    With Wb.Sheets("LaunchCodes")
        ' create a copy of your data (without header) in an unused column
        .Cells(2, "D").CurrentRegion.Copy .Cells(1, TmpClm)
        .Cells(1, TmpClm).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlNo
        Arr = .Cells(1, TmpClm).CurrentRegion.Value
        .Columns(TmpClm).ClearContents
    End With
    
    With ComboMid
        .List = Arr
        .ListIndex = 0                      ' assign first list item to Value
    End With
End Sub

You don't need to clear the combo box in the above code because replacing the List property with a new array automatically removes whatever it was before.