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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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