Sort contents of dropdown list

[IU.Hacker]

Suppose I create an everyday dropdown list in excel, and I wish to sort the contents (the choices in the list itself). How would I go about doing this?

I considered sorting the range which I draw the dropdown from, however I find this possibly a headache since the cells I'm drawing upon are all =IF() formulas, and which could splice my formulas in doing so.

However, any recommendations accepted, as I can't say I know this will happen since I do not know how to sort cells based on formula result rather than the actual contents themselves.


Thank you very much.

 

[MickG]

Hi, This code places the sheet List (In this case Column "K") used for your Combobox in an Array. Sorts the Array them places the results in your Combobox.
NB:- My test used Numeric values as the result of Formula for the sheet list

Sub ComSort()
Dim Aray, I As Integer, J As Integer, Temp2 As String
Aray = Range(Range("K1"), Range("K" & Rows.Count).End(xlUp))

For I = 1 To UBound(Aray, 1) 
   For J = I To UBound(Aray, 1)
        If Val(Aray(J, 1)) < Val(Aray(I, 1)) Then
            Temp2 = Aray(I, 1)
                Aray(I, 1) = Aray(J, 1)
                  Aray(J, 1) = Temp2
         End If
   Next J
Next I

With ActiveSheet.ComboBox1
    .Clear
    .List = Aray
    .ListIndex = 0
End With
End Sub

Mick

 

[IU.Hacker]

Ah, any tips if im using data validation? sorry to mention that, royal mess-up on my part there I know. But I'm using data validation unfortunately.

 

[MickG]

Hi Try this:-
This Code now sorts By Data & Formula.
Alter Code "Rng" Range To suit Your List address.

Sub Fsort()
Dim rng As Range, Temp2 As String
Dim Dn1 As Range, Dn2 As Range
Set rng = Range(Range("k1"), Range("k" & Rows.Count).End(xlUp))

For Each Dn1 In rng
  For Each Dn2 In rng
        If Val(Dn1) < Val(Dn2) Then
             Temp2 = Dn2.Formula
                Dn2.Formula = Dn1.Formula
                  Dn1.Formula = Temp2
         End If
   Next Dn2
Next Dn1

End Sub

Mick