Change Recorded Macro To Used Range

[Dazzawm]

I have recorded the macro below but I want to use it on different files with different amount of rows. What do I need to do for it to work on a used range rather than a set amount of rows like below please? Also the name of the sheet to be active sheet rather than a named sheet.

Sub Macro5()
    ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Add2 Key:=Range("A2:A2456" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet").Sort
        .SetRange Range("A1:T2456")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

 

[Dazzawm]

Does this do what you want?

Sub MySort()

    Dim lr As Long
    Dim rng1 As Range
    Dim rng2 As Range
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Set ranges
    Set rng1 = Range("A2:A" & lr)
    Set rng2 = Range("A1:T" & lr)
   
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=rng1, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange rng2
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
   
End Sub

Yes that works, much obliged to you squire.

 

[Joe4]

You are welcome.