How would I get drop down (validation lists) within Excel to display its results alphabetically?

I am using drop down lists with Excel to list the contents of cells A3 to A30.

The contents of these cells are not in aphetical order, therefore are shown within the drop down list in the same, none alphabetical order.

I know that one solution to getting it in aphetical order is to simply re-arrange the date in cells A3 to A30.  However, I have a few macro's running in the background of this and also some naming structures which would need altering also.  As I am going to be expanding this selection, from A3 to A30 to A31 then A32 etc.

I wondered if there was some VB code which would automatically order the drop down list in aphetical order?  if so, please will you help me?
Who is Participating?
Chris BottomleyConnect With a Mentor Commented:
AFAIK, the source data itself needs to be ordered so one potential solution is to create a range somewhere that is a clone of the master data but which can be sorted on change.

i.e. Detect any change to the range of the master data.  Use the detection to copy teh fiull range to the cloned area and sort in situ.

If you upload a copy of the workbook with a suggestion as to where the data copy can go, (new sheet if you prefer) and someone I am sure will have a go.

Rory ArchibaldConnect With a Mentor Commented:
There is probably no easy solution to this other than to sort the source data. Otherwise you would need to unlink the data validation from the range and instead hardcode a sorted string into each cell that used data validation to act as the list source. You would then have the problem of keeping the DV for each cell synchronised with your source list. Can you explain more as to why you can't sort the data?
As an alternative, you could use a combobox control as shown here:
Jeff DarlingConnect With a Mentor Developer AnalystCommented:
In VB there is a property on the list box to sort, However, in Excel VBA there is no sort.  you would need to sort manually.

Here is a function that will sort a listbox.

Here is a link to a similar question that I answered regarding sorting a Excel listbox.
Public Sub SortListBox()
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    With Me.ListBoxA
        For i = 0 To .ListCount - 2
            For j = i + 1 To .ListCount - 1
                If .List(i) > .List(j) Then
                    Temp = .List(j)
                    .List(j) = .List(i)
                    .List(i) = Temp
                End If
            Next j
        Next i
    End With
End Sub

Open in new window

evansjamAuthor Commented:
Thanks everyone, this is very useful to me.  I have tried each of your solutions and they work.  However, I think that for ease of use I will use Chris Bottomley's idea (hence the extra points).
All Courses

From novice to tech pro — start learning today.