VBA Sort A-Z

[harbour1302]

Hi Guys,

I require a worksheet change vba code;

I have names in column A (A3:A72) All i need is a code that automatically sorts the data in A3:A72 in alphabetical order (A-Z) whenever a new entry is entered in any of cells A3:A72.

I've looked online but couldn't find a sample code to do exactly what i require.

Thanks
Lewis

 

[ranman256]

Sub SortThem()
    Columns("A:A").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A3:A72")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

if you want the sort to happen instantly,
paste this code in the THISWORKBOOK object in the project view.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim isct As Range


Set isct = Application.Intersect(Target, Range("A3:A72"))
If (Not (isct Is Nothing)) Then SortThem
End Sub

 

[ranman256]

THISWORKBOOK code goes in:
open the VBE window: Alt-F11,
in the far left PROJECT window pane,
under the VBAProject (your workbook)
dbl-click the ThisWorkbook object,
paste the code there.