Change range in a sort macro

[ERed1]

Hi all,

I am working on this sheet and I need a lot of data to be sorted in a specific manner. The only issue is that the data I use changes. One week I may have 5000 rows of data in Excel, and others I may have 6000. Is there a way to make the sorta range in this code here change based on how many rows there are?

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

In the code above I tried changing all of the "A1:AW7000" to "Cells.Select" but that didn't work. All help is appreciated, thanks!

 

[MoshiM]

Do you really need sort every cell in the worksheet or is it every cell in the used range? You could try using a range object like

Dim My_Range as range
set MY_Range= activesheet.usedrange

and then use that in place of your range

 

[DanteAmor]

Hi all,

I am working on this sheet and I need a lot of data to be sorted in a specific manner. The only issue is that the data I use changes. One week I may have 5000 rows of data in Excel, and others I may have 6000. Is there a way to make the sorta range in this code here change based on how many rows there are?

    Cells.Select    ActiveWorkbook.Worksheets("REPORT").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("REPORT").Sort.SortFields.Add2 [COLOR=#ff0000]Key:=Range( _
        "A1:AW7000")[/COLOR], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("REPORT").Sort.SortFields.Add2 [COLOR=#ff0000]Key:=Range( _
        "A1:AW7000")[/COLOR], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("REPORT").Sort.SortFields.Add2[COLOR=#ff0000] Key:=Range( _
        "A1:AW7000")[/COLOR], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("REPORT").Sort
        .SetRange Range("A1:AW7000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

In the code above I tried changing all of the "A1:AW7000" to "Cells.Select" but that didn't work. All help is appreciated, thanks!

In your code you have the same range in each key to order.
So you don't need 3 keys, it can be like this:

Sub test()
  Range("A1:AW" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
End Sub

 

[ERed1]

In your code you have the same range in each key to order.
So you don't need 3 keys, it can be like this:

Sub test()
  Range("A1:AW" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
End Sub

Thank you. I got this to work perfectly.

 

[DanteAmor]

Thank you. I got this to work perfectly.

I'm glad to help you. Thanks for the feedback.