Using the VBA codes, drop-down lists that filled with unique and the sorted alphabetically values can be created.
The items of the drop-down lists can be populated in from the same sheet or from other sheet.
When a modify is made to list items (add, delete, etc.), this modify can automatically be seen in the drop-down lists.
Excel found unreadable content in ... :
When I opened the workbook containing the data validation lists that I created using VBA codes, I was encountering this problem. On this, I added code snippet to delete data validation lists while closing the workbook :
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeAllValidation).Validation.Delete
On Error GoTo 0
Next ws
End Sub
I also added a code so that the items to be added to the data validation list do not exceed 255 characters.
Details , sample file at : Vba data validation list