I'm a newbee when it comes to macros. I inherited a spreadsheet with a bunch of macros and have successfully ported the macros to a new worksheet. A change that I can't figure out is how to have a variable range when doing a sort. The macro is below. The data that it works with is variable in length. Currently the sort is limited to the first 300 rows. I want to change that so it sorts through all the data, but that changes daily when the macro is run. How would I code it for a variable range (blank after the last row).
Sub Correct_Backlog_Sort()
'
' Correct_Backlog_Sort Macro
' Resort based on corrected backlog
'
'
Cells.Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("C2:C300") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
"AE2:AE300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
"AF2:AF300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:AF300")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Bookmarks