Populate and Sort a ComboBox with visible unique values from an Excel table

Good Morning,
I'm new here. My name is Edoardo.
I'm using this code for populing a ComboBox with visible unique values from an Excel table filtered:

Private Sub UserForm_Initialize()

Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp As Range
Dim i As Single

On Error Resume Next
Set temp = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Range

For i = 2 To temp.Cells.Rows.Count
If Len(temp.Cells(i)) > 0 And Not temp.Cells(i).EntireRow.Hidden Then
test.Add temp.Cells(i), CStr(temp.Cells(i))
End If
Next i

UserForm1.ComboBox1 = Clear

For Each Value In test
UserForm1.ComboBox1.AddItem Value
Next Value

Set test = Nothing

End Sub

That code works but I would like the values displayed in the ComboBox to be also sorted progressively. Can someone help me?
My english is not perfect because I'm italian, I apologize.

Thanks a lot to anyone who will help me
: pray2::hi:

Code:

---

Option Explicit


Private Sub UserForm_Initialize()
    Dim a As Object
    Dim t As ListObject
    Dim rr As Range, r As Range
    Dim s As String
   
    Set a = CreateObject("system.collections.arraylist")
    Set t = Worksheets("Sheet1").ListObjects("Table1")

    With t.Range
        .AutoFilter 1, "<>"
        On Error Resume Next
        Set rr = Intersect(.Offset(1), .Columns(1).SpecialCells(xlCellTypeVisible))
        On Error GoTo 0
        If Not rr Is Nothing Then
            For Each r In rr
                s = CStr(r.Value)
                If Not a.contains(s) Then
                    a.Add s
                End If
            Next
            a.Sort
            ComboBox1.List = a.toarray
        End If
    End With
     
End Sub

---

マナ

Quote:

---

Originally Posted by mana

Code:

---

Option Explicit


Private Sub UserForm_Initialize()
    Dim a As Object
    Dim t As ListObject
    Dim rr As Range, r As Range
    Dim s As String
   
    Set a = CreateObject("system.collections.arraylist")
    Set t = Worksheets("Sheet1").ListObjects("Table1")

    With t.Range
        .AutoFilter 1, "<>"
        On Error Resume Next
        Set rr = Intersect(.Offset(1), .Columns(1).SpecialCells(xlCellTypeVisible))
        On Error GoTo 0
        If Not rr Is Nothing Then
            For Each r In rr
                s = CStr(r.Value)
                If Not a.contains(s) Then
                    a.Add s
                End If
            Next
            a.Sort
            ComboBox1.List = a.toarray
        End If
    End With
     
End Sub

---

マナ

---

Thanks Mana for your reply, but your code doesn't work as I would like. In the previous message maybe I was not clear enough.

After I've applied a filter to column 1, I would like to see, in the combobox, just the filtered data, without duplicates and in progressive sort. Instead, with your code, in the combobox, I see all data in column 1 (without duplicates and in progressive sort), even when I'm using a filter.

Anyway, thank a lot for your reply
:hi:

Code:

---

Option Explicit


Private Sub UserForm_Initialize()
    Dim a As Object
    Dim t As ListObject
    Dim rr As Range, r As Range
    Dim s As String
   
    Set a = CreateObject("system.collections.arraylist")
    Set t = Worksheets("Sheet1").ListObjects("Table1")

    On Error Resume Next
    Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0


    If Not rr Is Nothing Then
        For Each r In rr
            s = CStr(r.Value)
            If Not a.contains(s) Then
                a.Add s
            End If
        Next
        a.Sort
        ComboBox1.List = a.toarray
    End If
   
End Sub

---

Quote:

---

Originally Posted by mana

Code:

---

Option Explicit


Private Sub UserForm_Initialize()
    Dim a As Object
    Dim t As ListObject
    Dim rr As Range, r As Range
    Dim s As String
   
    Set a = CreateObject("system.collections.arraylist")
    Set t = Worksheets("Sheet1").ListObjects("Table1")

    On Error Resume Next
    Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0


    If Not rr Is Nothing Then
        For Each r In rr
            s = CStr(r.Value)
            If Not a.contains(s) Then
                a.Add s
            End If
        Next
        a.Sort
        ComboBox1.List = a.toarray
    End If
   
End Sub

---

---

Thanks Mana, it works perfectly. It's exactly what I wanted!
:clap::hi: