Very fast méthod : 0.1s for 10.000 items
http://boisgontierjacques.free.fr/fichiers/F_liste_triee.xls
Private Sub UserForm_Initialize()
Dim temp()
temp = Range("liste") ' liste tableau temp (1 To n,1 To 1)
' ou temp = Range([B2], [B2].End(xlDown))
Call tri(temp, 1, UBound(temp, 1))
Me.ListBox1.List = temp
End Sub
Sub tri(a(), gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
Do While a(g, 1) < ref: g = g + 1: Loop
Do While ref < a(d, 1): d = d - 1: Loop
If g <= d Then
temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
JB
http://boisgontierjacques.free.fr/
There's nothing built in that will let you do this.
One way I've used is to:
Insert a temporary worksheet
copy the column of data to column A of that new sheet
then use advanced filter (unique records only) and put it into column B
And pick up the values in B2:B### (header in B1).
There are other techniques like using a collection and looping through the
cells.
John Walkenbach shows how (for a different purpose) here:http://spreadsheetpage.com/index.php/file/unique_items_in_a_listbox/
I think John wanted the list of unique items since he asked about what hesaw in
the autofilter dropdown.
Not just a sorted list.
Very fast méthod : 0.1s for 10.000 items
Private Sub UserForm_Initialize()
Dim temp()
temp = Range("liste") ' liste tableau temp (1 To n,1 To 1)
' ou temp = Range([B2], [B2].End(xlDown))
Call tri(temp, 1, UBound(temp, 1))
Me.ListBox1.List = temp
End SubSub tri(a(), gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
Do While a(g, 1) < ref: g = g + 1: Loop
Do While ref < a(d, 1): d = d - 1: Loop
If g <= d Then
temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
--
Dave Peterson- Masquer le texte des messages précédents -
- Afficher le texte des messages précédents -
http://boisgontierjacques.free.fr/fichiers/ListeSansDoublonsDictionaire.xls
JB
I think John wanted the list of unique items since he asked about what he saw in
the autofilter dropdown.
Not just a sorted list.
Very fast méthod : 0.1s for 10.000 items
Private Sub UserForm_Initialize()
Dim temp()
temp = Range("liste") ' liste tableau temp (1 To n,1 To 1)
' ou temp = Range([B2], [B2].End(xlDown))
Call tri(temp, 1, UBound(temp, 1))
Me.ListBox1.List = temp
End SubSub tri(a(), gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
Do While a(g, 1) < ref: g = g + 1: Loop
Do While ref < a(d, 1): d = d - 1: Loop
If g <= d Then
temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
There's nothing built in that will let you do this.One way I've used is to:
Insert a temporary worksheet
copy the column of data to column A of that new sheet
then use advanced filter (unique records only) and put it into column B
And pick up the values in B2:B### (header in B1).There are other techniques like using a collection and looping through the
cells.John Walkenbach shows how (for a different purpose) here:http://spreadsheetpage.com/index.php/file/unique_items_in_a_listbox/John wrote:Is is possible to capture AutoFilter Items in an Array via VBA?If one clicks on an autofilter dropdown you can see a laundry list of items
(visible list is limited to 1,000 items).
Dave Peterson
--
Dave Peterson- Masquer le texte des messages précédents -
- Afficher le texte des messages précédents -