I have found the code below (using record macro) but I want the row 1208 to be s

Question

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

Explanation / Answer

Try this.

Function sortby()
Dim Lastrow As Long
Lastrow = Cells(Cells.Rows.Count, "K").End(xlUp).Row

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("K1:K" & Lastrow), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:K" & Lastrow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Function

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects