Populate combobox with unique value from a range

[haseft]

hej,
I want to populate combobox with unique value from a range.
the code working not properly, combobox not sorted and fylls allso with empty line (se the result/picture), (the code works only if the value is in a column and do not hanve eny empy cell)
thanks for help,
here is the code and the data.

Dim UfDic As Object
Dim Cl As Range

Set UfDic = CreateObject("scripting.dictionary")
UfDic.CompareMode = 1
With Sheets("Sheet1")
  For Each Cl In .Range("AG2", "AI17")
    If Not UfDic.exists(Cl.Value) Then UfDic.Add Cl.Value, CreateObject("scripting.dictionary")
      Set UfDic(Cl.Value)(Cl.Offset(, -1).Value) = Cl
  Next Cl
End With
Me.Page8ComboBox1.List = UfDic.keys

Year1	Year2	Year3
2017	2018	2019
2020	2021	2022
2020	2021	2022
2018
2015
2016
2017	2018
2020
2015
2016
2017
2018	2019	2020
2019
2019
2018
2021	2022

 

[JEC]

Without any object

Private Sub UserForm_Initialize()
  Dim ar As Variant, tmp As Variant, i As Long, j As Long, x As Long, y As Long, a As Long
  Dim sq() As Variant
  
  ar = Sheets(1).Range("AG2:AI17")
  
  For i = 1 To UBound(ar) * UBound(ar, 2)
     x = (i - 1) \ 3 + 1
     y = (i - 1) Mod 3 + 1
     If InStr(Join(sq, "|"), ar(x, y)) = 0 Then
        ReDim Preserve sq(a)
        sq(a) = ar(x, y): a = a + 1
     End If
  Next
  
  For i = 0 To a - 1
     For j = i + 1 To a - 1
        If sq(j) < sq(i) Then tmp = sq(j): sq(j) = sq(i): sq(i) = tmp
    Next
 Next
  
 Me.Page8ComboBox1.List = sq
End Sub

 

[JEC]

Or

Private Sub UserForm_Initialize()
  Dim ar As Variant, tmp As Variant, i As Long, j As Long, x As Long, y As Long, a As Long
  Dim sq() As Variant
  
  For Each it In Sheets(1).Range("AG2:AI17")
     If InStr(Join(sq, "|"), it.Value) = 0 Then
        ReDim Preserve sq(a)
        sq(a) = it.Value: a = a + 1
     End If
  Next
  
  For i = 0 To a - 1
     For j = i + 1 To a - 1
        If sq(j) < sq(i) Then tmp = sq(j): sq(j) = sq(i): sq(i) = tmp
     Next
  Next
  
  Me.Page8ComboBox1.List = sq
End Sub

 

[haseft]

Or

Private Sub UserForm_Initialize()
  Dim ar As Variant, tmp As Variant, i As Long, j As Long, x As Long, y As Long, a As Long
  Dim sq() As Variant
 
  For Each it In Sheets(1).Range("AG2:AI17")
     If InStr(Join(sq, "|"), it.Value) = 0 Then
        ReDim Preserve sq(a)
        sq(a) = it.Value: a = a + 1
     End If
  Next
 
  For i = 0 To a - 1
     For j = i + 1 To a - 1
        If sq(j) < sq(i) Then tmp = sq(j): sq(j) = sq(i): sq(i) = tmp
     Next
  Next
 
  Me.Page8ComboBox1.List = sq
End Sub

hej JEC,
tanks,
your first codes works very nice.
the second code, get error on linje

For Each it In Sheets(1).Range("AG2:AI17")

It says Variable "it" not defined.

 

[JEC]

Ahh yes , add a line at top: Dim It as variant

 

[haseft]

Ahh yes , add a line at top: Dim It as variant

hi JEC,
the second codes works nice too, thans for help
with the bellow code I want to preselect Page8ComboBox1 with current year,
but not working, the combobox contain value year 2021, but do not preselect.

Page8ComboBox1.Value = Year(Date)

 

[JEC]

What you want is only possible with a listbox.
Add a listbox1 to your userform and try this. This does not set default value, it just preselects

Private Sub UserForm_Initialize()
 Dim i As Long
 With ListBox1
   .List = Array(2020, 2021, 2022, 2023)
    For i = 0 To UBound(.List)
      If .List(i) = Year(Date) Then .Selected(i) = True: Exit For
    Next
 End With
End Sub

 

[haseft]

What you want is only possible with a listbox.
Add a listbox1 to your userform and try this. This does not set default value, it just preselects

Private Sub UserForm_Initialize()
 Dim i As Long
 With ListBox1
   .List = Array(2020, 2021, 2022, 2023)
    For i = 0 To UBound(.List)
      If .List(i) = Year(Date) Then .Selected(i) = True: Exit For
    Next
 End With
End Sub

it works nice, tanks for help JEC,