Help with sort Macro

[JohnCG]

I am new to using macros and have been using the record macro feature to try to help but I can not figure out how to modify what excel recorded into what I would like to do.

Sub Macro13()
'
' Macro13 Macro
'

'
    Range("J2:J20").Select
    ActiveWorkbook.Worksheets("Calculator").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Calculator").Sort.SortFields.Add Key:=Range("J9") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Calculator").Sort
        .SetRange Range("J2:J20")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

The issue is I do not know how to change the range because column J will not always end at J20, the real file I need to use this on varies but it typically ends at J320000. Is there a way to set the range until there are no more values in a cell within that column. Any help would really be appreciated.

 

[jeffreybrown]

You can add a variable called last row

Sub Macro13()
[COLOR="#FF0000"]    Dim rng As Range
    Dim LR As Long: LR = Range("J" & Rows.Count).End(xlUp).Row[/COLOR]    
    ActiveWorkbook.Worksheets("Calculator").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Calculator").Sort.SortFields.Add Key:=Range("J9") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Calculator").Sort
        .SetRange Range("J2:J" & [COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]LR[/COLOR])
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Mahesh_P]

Try :

Sub Mtest()
Range("J9:J" & Cells(Rows.Count, 10).End(xlUp).Row).Select
Selection.Sort _
Key1:=Range("J9"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

 

[JohnCG]

Thank You! That did exactly what I needed.