Sort contents of dropdown list

IU.Hacker

New Member
Joined
Mar 25, 2009
Messages
3
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
Code:
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

New Member
Joined
Mar 25, 2009
Messages
3
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi Try this:-
This Code now sorts By Data & Formula.
Alter Code "Rng" Range To suit Your List address.
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,959
Members
431,689
Latest member
jacker01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top