Populate UserForm ComboBox List With Unique Values From Multiple Range

[ShumsFaruk]

Guys,

I was busy with some project and found bits and pieces VBA codes by which I made below code more useful and thought of sharing.

Add below code in UserForm_Initialize

Private Sub UserForm_Initialize()
Dim LastRow1 As Long, LastRow2 As Long
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim v1, v2, e1, e2
Set Ws1 = Worksheets("ABC") ' Change as you have
Set Ws2 = Worksheets("XYZ") ' Change as you have
LastRow1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row ' Change the column as required
LastRow2 = Ws2.Range("A" & Rows.Count).End(xlUp).Row ' Change the column as required

'Below code centers the appearance of userform on any screen
With Application
    .WindowState = xlMaximized
    
End With
With Me
    
    .Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
    .Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
End With

'Empty ComboBox1 (Change the ComboBox1 name as you have)
ComboBox1.Clear

'Fill ComboBox1 (Change the ComboBox1 name as you have)
With Ws1.Range("A3:A" & LastRow1) 'Change the Column of which you need the list in ComboBox
    v1 = .Value
End With
With Ws2.Range("A3:A" & LastRow2) 'Change the Column of which you need the list in ComboBox
    v2 = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e1 In v1
        If Not .exists(e1) Then .Add e1, Nothing
    Next
    For Each e2 In v2
        If Not .exists(e2) Then .Add e2, Nothing
    Next
    If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With

ComboBox1.SetFocus
End Sub

Hope its helpful for many struggling learners like me.