PDA

View Full Version : Populate and Sort a ComboBox with visible unique values from an Excel table



edoardodf
06-14-2018, 04:27 AM
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:

mana
06-14-2018, 06:06 AM
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




マナ

edoardodf
06-14-2018, 07:15 AM
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:

mana
06-14-2018, 08:05 AM
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

edoardodf
06-14-2018, 08:42 AM
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: