Help With Monotonous Sort Code

[Dazzawm]

I have to sort many thousand rows by selection but each time I select the rows i want to sort the sort criteria keeps resetting itself. I done a recorded macro but obviously the range of rows change each time. What needs changing in code for the sort to work on my selected rows each time?

Thanks

Sub Macro2()
'
' Macro2 Macro
'


'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "C1037:C1040"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "AJ1037:AJ1040"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "AH1037:AH1040"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1037:AX1040")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

 

[steve the fish]

This will sort the range based on your selection provided you are on 'Sheet1'

If ActiveSheet.Name = "Sheet1" Then
    fr = Selection.Row
    lr = Selection.Row + Selection.Rows.Count - 1
    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range(Cells(fr, "C"), Cells(lr, "C")), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range(Cells(fr, "AJ"), Cells(lr, "AJ")), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range(Cells(fr, "AH"), Cells(lr, "AH")), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range(Cells(fr, "A"), Cells(lr, "AX"))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End If

 

[Dazzawm]

Thanks Steve. Do I just make a module as normal as it has no start or end sub? I was going to put button on taskbar so click it after selecting range.

 

[Dazzawm]

Yes I do. Thanks works great.