sorting every cell with data in excel using VBA

Question

I recorded a macro for sorting this sheet. how do I replace the hardcoded ranges so that this script works with any number of rows?

Worksheets(1).sort.SortFields. _
    Clear
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("A2:A130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("B2:B130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("C2:C130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("D2:D130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("E2:E130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("P2:P130008"), SortOn:=xlSortOnValues, Order:= _
    xlDescending, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
    Add2 Key:=Range("Q2:Q130008"), SortOn:=xlSortOnValues, Order:= _
    xlDescending, DataOption:=xlSortNormal
With Worksheets(1).sort
    .SetRange Range("A1:R130008")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Answer 1

This ought to do the job. Please try it.

Sub StackOverflow()

    Dim Rng         As Range            ' range to sort
    
    With Worksheets(1)
        Set Rng = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) _
                  .Resize(, .Columns("R").Column)
        ' .Columns("R").Column = 18 and may be replaced with this number
        
        With .Sort.SortFields
            .Clear
            ' specify the columns to sort on in sequence of priority
            .Add2 Key:=Rng.Cells(1), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Add2 Key:=Rng.Cells(2), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Add2 Key:=Rng.Cells(3), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Add2 Key:=Rng.Cells(4), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Add2 Key:=Rng.Cells(5), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Add2 Key:=Rng.Cells(16), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
            .Add2 Key:=Rng.Cells(17), SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
        End With
        With .Sort
            .SetRange Rng
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Note that in .Sort.SetRange the entire range must be specified whereas for setting each of the Keys just one cell is required to identify a column. Since the cells in a range are numbered 1 and up first across and then down the first 18 cell numbers in the range coincide with the column numbers in the underling sheet range. So Key:=Rng.Cells(1) happens to be Worksheets(1).Cells(2, "A") based on how Rng was specified. I chose the shorter syntax but the effect is that the entire range will first be sorted on column A before other keys are applied.