# Sort contents of dropdown list

#### IU.Hacker

##### New Member
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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

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.

Hi Try this:-
This Code now sorts By Data & Formula.
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

Replies
3
Views
273
Replies
5
Views
148
Replies
0
Views
521
Replies
4
Views
159
Replies
1
Views
255

1,216,312
Messages
6,130,009
Members
449,550
Latest member
8073662045

### 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.

### Which adblocker are you using?

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

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