Sorting A-Z in an 1-column array which has variable members

[jstieven]

Chandoo -- I have a 1-column array which can have anywhere between 25 - 100 words (text). I would like to write a macro to sort this array from A-Z. The problem is if you sort the entire 100 cell array (assuming the words were not put in a-Z order) and there is only 25 words, you get 75 spaces and then the 25 words in A-Z order!! How can I get this to work? Thanks! John

 

[Luke M]

How's this? I assumed you had a header row.

[pre]

Sub SortMe()
Dim LastRow As Integer
'Find the last row of data
LastRow = Range("A65536").End(xlUp).Row

Range("A1", Cells(LastRow, "A")).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

[/pre]