VBA code to sort rows in alphabetical order

[wrecclesham]

Could someone please help me to create a small VBA script that is able to automatically sort the 10 rows contained within range A1:C10 in ascending alphabetical order, using the contents of cells A1:A10?

Also, if I record a macro while manually performing the above sort just using the Sort button, should this work in theory and would it produce clean VBA code, or is it sometimes better to manually write the code, instead of using the Record Macro feature? I've always wondered about that.

Here's how my test worksheet looks:

 

[Joe4]

Also, if I record a macro while manually performing the above sort just using the sort button, should this work in theory and would it produce clean VBA code, or is it sometimes better to manually write VBA code, instead of using the record feature? I've always wondered about that.

In this case, since it is a defined range (and not a dynamically changing one), recording a macro would work fine.

 

[wrecclesham]

Great. Thanks.

How can I make it so that it runs whenever any values in the worksheet are updated?

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:C10").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A1:A10") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A10")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Joe4]

Put it inside a Worksheet_Change event procedure.

See: https://www.thesmallman.com/vba-worksheet-change-events

 

[wrecclesham]

I ended up using the below code instead.

It works great and is shorter than the VBA I got when I recorded the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A1:C10").Sort Key1:=Range("A1"), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

End Sub

 

[Joe4]

It works great and is shorter than the VBA I got when I recorded the macro.

Excellent!

Yes, recorded code can often be cleaned up to make it a little shorter/more efficient, thought it would still work the other way too.