Filter columns (number of rows keeps changing)

[gomes123]

I have a table starting from cell A2-L2. I would like to filter column J, from the largest to the smallest (descending order, the data is numerical) - starting from J2 (which is the heading), and the data is from J3 onwards. The end of the data changes, for now it is up to J1015 - which my macro works fine. But how would I modify the excel macro below to automatically filter all the data in column J if the last row of the table changes (for example to J1200)? Thanks

Sub Macro1()


        Selection.AutoFilter
    ActiveSheet.Range("$A$2:$L$1015").AutoFilter Field:=9, Criteria1:="YES"
    ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "J2:J1015"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Main").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    

End Sub

 

[myall_blues]

You can find the 'last' used row and substitute that in. Take care if the data are already filtered as it returns the last visible row.

Sub Macro1()
dim LastRow as Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Selection.AutoFilter
ActiveSheet.Range("$A$2:$L$"& LastRow).AutoFilter Field:=9, Criteria1:="YES"
ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Add Key:=Range( _
"J2:J" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Main").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub