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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
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 DarlingDeveloper 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

Chris BottomleySoftware Quality Lead EngineerCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.