One way
Option Explicit
Private dic As Object
Private Sub UserForm_Initialize()
Dim a, b, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
dic.CopareMode = 1
a = [d19].CurrentRegion.Value
b = [f19].CurrentRegion.Value
For i = 2 To UBound(a, 1)
If Not dic.exists(a(i, 1)) Then
Set dic(a(i, 1)) = CreateObject("System.Collections.ArrayList")
End If
For ii = 3 To UBound(b, 1)
If b(ii, i - 1) <> "" Then dic(a(i, 1)).Add b(ii, i - 1)
Next
Next
Me.ComboBox1.List = dic.keys
End Sub
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
Me.ComboBox2.List = dic(Me.ComboBox1.Value).ToArray
End Sub
Bookmarks