Excel Vba Range Sort How To Set That
Excel Vba Range Sort Free Program To

Its a complete guide that covers handling invalid inputs and applying drop-downs to other cells.

Suliman, Wim, and Colin wanted to know if it is possible to automatically sort the drop-downs whenever new entries are added to the source range.

And while I mention a macro as the solution in my response to the question, there are actually a variety of ways to accomplish this.

Excel Vba Range Sort How To Set That

If you want to learn more about events that can trigger a macro to run and how to set that up, check out this tutorial on VBA Code Modules How to Run Macros Based on User Events.

Its best to use an Excel Table and named range to reference the entire Table column.

I explain that setup in my previous post on How to Add New Rows to Drop-down Lists Automatically.

Anyone using a desktop version of Excel on either Windows or Mac should be able to use a macro to automatically sort their drop downs.

So this can be a good option if you dont want to make changes to your source sheet.

This means there is no additional setup work required within the Excel file.

All of the other solutions mentioned will require setup work with either a macro, Power Query, or formulas.

The add-in has some other great features, and was created to help you save time entering data into cells when you have long data validation lists.

I recommend you get acquainted with Power Query by checking out this overview I put together.

You can also use an external source like a database or website for your query.

This produces a list of unique entries, sorted in alphabetical order.

You will still want to use the named range technique to reference the Table column for data validation, and I explain more in this post on drop-down lists that automatically update.

The drop-downs that use the Table as a source range will automatically display the changes in the sorted list.

Unfortunately this feature is only accessible on the Insiders Fast Build on Office 365.

Excel Vba Range Sort Free Program To

It is a free program to all Office 365 subscribers, and I explain more about it in this post on Dynamic Array Formulas.

This formula will return a sorted list in the spill range (the blank cells below the formula).

This is done by referencing the first cell of the spill range, then adding a symbol after it.

Since the spill range is referenced in the source of the data validation, the drop-down list will also automatically update.

Then reference that range as the source of the data validation.

While the dynamic array option is by far the coolest and simplest, its not currently accessible to everyone.

Excel For Mac Vba Range Sort