Alphabetize drop down list

[KDS14589]

I've used MrExcel's Trick #916 to remove blanks from my listbut now I want the results to be alphabetized without rearranging the primarylist after each time I add data, the data is all single text and no numbers.

 

[Logit]

[h=2]Alphabetize drop down list[/h]

Is this a Data Validation dropdown ?

 

[KDS14589]

is this a data validation dropdown ?

yes

 

[Logit]

.
Lets say you have your list in Col K ...

If you place this macro in the Sheet Module, it will auto-sort anything that is entered into Column K, Alphabetically from A - Z.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next




    If Not Intersect(Target, Range("K:K")) Is Nothing Then
        Range("K1").Sort Key1:=Range("K1"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
        Application.DisplayAlerts = True
    End If
End Sub

 

[KDS14589]

.
Lets say you have your list in Col K ...

If you place this macro in the Sheet Module, it will auto-sort anything that is entered into Column K, Alphabetically from A - Z.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next




    If Not Intersect(Target, Range("K:K")) Is Nothing Then
        Range("K1").Sort Key1:=Range("K1"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
        Application.DisplayAlerts = True
    End If
End Sub

I may have been wrong when I said ‘YES’ because I can’t get the macro in the Sheet Module to work.
The data is from a formula I used from ‘MrExcel Trick #916 to remove blanks. FYI the final data I’m using is in column I

 

[KDS14589]

I may have been wrong when I said ‘YES’ because I can’t get the macro in the Sheet Module to work.
The data is from a formula I used from ‘MrExcel Trick #916' to remove blanks. FYI the final data I’m using is in column I.
I'm trying to get my final result to be alphabetized.

​