Sort data in multiple columns without a fixed range

[j4ttlife]

Hi, I'm trying to use a macro that sorts data in multiple columns.

However, upon viewing the code after recording the macro, I notice a range has been declared (to row 483).

Issue with the below code is the ranges only go to row 483. Next week it may be 500+ etc


Original recorded Macro

Cells.Select
    ActiveWorkbook.Worksheets("Oct data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Oct Data").Sort.SortFields.Add2 Key _
        :=Range("A2:A483"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Oct Data").Sort.SortFields.Add2 Key _
        :=Range("R2:R483"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Oct Data").Sort
        .SetRange Range("A1:AV483")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.Run "ConnectChartEvents"
    Sheets("Oct Data").Select

My attempt below:

Dim lastA As Long

    ActiveWorkbook.Worksheets("Oct Data").Sort.SortFields.Add2 Key _
        :=Range("A1:A" & lastA), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

 

[bferraz]

Hey, can you check if the code below works for you?

Dim lastRow As Long

With ActiveWorkbook.Worksheets("Oct data")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=.Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add2 Key:=.Range("R2:R" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With .Sort
        .SetRange .Range("A1:AV" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

Application.Run "ConnectChartEvents"
Sheets("Oct Data").Select

 

[Fluff]

How about

   Dim UsdRws As Long
   
   With Worksheets("Oct data").Sort
      UsdRws = .Worksheet.Range("A" & Rows.Count).End(xlUp).Row
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
         DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("R1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
         DataOption:=xlSortNormal
      
      .SetRange Range("A1:AV" & UsdRws)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

    Application.Run "ConnectChartEvents"
    Sheets("Oct Data").Select

 

[j4ttlife]

How about

   Dim UsdRws As Long
  
   With Worksheets("Oct data").Sort
      UsdRws = .Worksheet.Range("A" & Rows.Count).End(xlUp).Row
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
         DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("R1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
         DataOption:=xlSortNormal
     
      .SetRange Range("A1:AV" & UsdRws)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

    Application.Run "ConnectChartEvents"
    Sheets("Oct Data").Select

Thanks Fluff. I was receiving an error 438 but slightly ameneded the UsdRws variable range to the below and it has done the trick. Appreciate the help.

UsdRws = Range("A" & Rows.Count).End(xlUp).Row

 

[j4ttlife]

Hey, can you check if the code below works for you?

Dim lastRow As Long

With ActiveWorkbook.Worksheets("Oct data")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=.Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add2 Key:=.Range("R2:R" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With .Sort
        .SetRange .Range("A1:AV" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

Application.Run "ConnectChartEvents"
Sheets("Oct Data").Select

Thanks for the reply. I tried the other solution provided and have managed to resolve my issue, but I appreciate your help.

 

[Fluff]

I was receiving an error 438

Oops that line should have been

      UsdRws = .Parent.Range("A" & Rows.Count).End(xlUp).Row

Your change may give the wrong value if the Oct Data sheet is not active.