Sort By Z-A descending using defined variable name for lastrow

0
votes

I have found the code below (using record macro) but I want the row 1208 to be set as the last active row. As I am sorting the columns by using column K whose values are row number (code line below):

ActiveSheet.Range("K1:K" & last).Formula = "=row()"

I want to use this K range in the sortby() code below. Can I do this using the variable called "last" or do I have to use the "find lastrow" code?

Function sortby()

Columns("A:D").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("K1:K1208"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:K1208")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function
1
vbasortingexcelexcel-formulaformula
How / what value do you assign to your last / find lastrow variable? – Gareth

1 Answers

0
votes

I generally use this line to find the last active row:

LastRow = Sheets(ShtName).UsedRange.Rows.Count

You also don't have to specific the rows, assuming your header are in row 1. You can use code like this:

Set ws = Worksheets("Sheet1")

ws.Columns("A:K").Sort Key1:=ws.Range("K2"), Order1:=xlDescending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:= _
    xlSortNormal, DataOption2:=xlSortNormal