Sort Active Range

[Roggie]

I recorded a macro to sort my worksheet by certain colors and I noticed the range is hardcoded into the script. Since my range will change with each export, is there a way to edit the ranges to reflect the whatever the size of the worksheet is?

Sub SORT2()
'
' SORT2 Macro
'
' Keyboard Shortcut: Ctrl+j
'
    ActiveWorkbook.Worksheets("Candidates|All Attributes").SORT.SortFields.Clear
    ActiveWorkbook.Worksheets("Candidates|All Attributes").SORT.SortFields.Add( _
        Range("[B]A2:A322[/B]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(146, 208, 80)
    ActiveWorkbook.Worksheets("Candidates|All Attributes").SORT.SortFields.Add( _
        Range("[B]A2:A322[/B]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(155, 194, 230)
    With ActiveWorkbook.Worksheets("Candidates|All Attributes").SORT
        .SETRANGE Range("[B]A1:AJ322[/B]")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[MARK858]

Maybe...

Sub SORT2()
    '
    ' SORT2 Macro
    '
    ' Keyboard Shortcut: Ctrl+j
    '
    lr = Worksheets("Candidates|All Attributes").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

    ActiveWorkbook.Worksheets("Candidates|All Attributes").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Candidates|All Attributes").Sort.SortFields.Add( _
            Range("A2:A" & lr), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(146, 208, 80)
    ActiveWorkbook.Worksheets("Candidates|All Attributes").Sort.SortFields.Add( _
            Range("A2:A" & lr), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(155, 194, 230)

    With ActiveWorkbook.Worksheets("Candidates|All Attributes").Sort
        .SetRange Range("A1:AJ" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub