Sorting items ListBox

[jajinder]

Hi,

Have an UserForm with a ListBox (Excel) containing unique values from colmn B. I cannot do that manually, because it is linked with colmn A (leading). How do I sort the values in the ListBox? Mij code so far:

i = 1
With ComboBox2
Do Until Sheets("DB").Cells(i, 1).Value = Empty
.AddItem Sheets("DB").Cells(i, 1).Value
i = i + 1
Loop
For i = .ListCount - 1 To 1 Step -1
If .List(i) = .List(i - 1) Then .RemoveItem (i)
Next
End With

Greets Jajinder

 

[WillRiley]

Why not use the advanced filter to copy unique values from colB to another location in the workbook & use that sorted range as your listbox source ?

 

[WillRiley]

Here's an example

Sub Adv_Filter()
Dim lLastRow As Long
Sheet3.Range("B1:B" & Range("B65536") _
.End(xlUp).Row).ClearContents
lLastRow = Sheet4.Range("B65536").End(xlUp).Row
Sheet4.Range("B1:B" & lLastRow&) _
.AdvancedFilter xlFilterCopy, , Sheet3.Range("B1"), True
Sheet3.Range("B2:B" & Range("B65536").End(xlUp).Row) _
.Sort Key1:=Sheet3.Columns("B")
End Sub

Then use

Private Sub UserForm_Initialize()
i = 2
With ComboBox1
   Do Until Sheets("Sheet3").Cells(i, 2).Value = Empty
   .AddItem Sheets("Sheet3").Cells(i, 2).Value
   i = i + 1
Loop
End With

End Sub

to populate the combobox from sheet3's sorted & unique dala list as opposed to directly from the original list..

Hope this helps,

Will

 

[jajinder]

Hi, thanx for reply....
It is a very good solution, but I don't want to do that,'cause of the file-size. File contains about 500.000 values and formulas. More values / formulas will only increase the size. That's why I'm using VBA.

Greets Jajinder

 

[jajinder]

ow.... you are faster...

Greets Jajinder