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

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top