I have a sub that prioritizes a set of rows across a set of columns based on the sum value in each row.
Where I need help is with limiting the sort to only those rows that have a number (vs. blank) in column A. if Column A is blank, it doesn't sort. If there is, then it does sort. Right now it sorts 10 rows even if there is no data in a row.
Thank you in advance,
B.
Sub Prioritise()
Dim xLast_Row As Long
Sheets("Risk_Return").Activate
Range("E6").End(xlDown).Select
xLast_Row = ActiveCell.Row
Range("A7").Select
If xLast_Row = Cells.Rows.Count Then
MsgBox ("No data found - run cancelled.")
Exit Sub
End If
With Sheets("Risk_Return").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("E7:E" & xLast_Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("A6:E" & xLast_Row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Great job guys! I removed all of the protection code I had and it worked perfectly! Thanks R. for getting me tolearn how to "step through" code! <br /><br />Great Teamwork between the two of you.<br /><br />B.
Here's what I have now;
Option Explicit
Sub Prioritise()
Dim xLast_Row As Long
'ActiveSheet.Unprotect Password:="pass"
Sheets("Risk_Return").Acti
'Range("E6").End(xlDown).S
xLast_Row = Cells(Rows.Count, "A").End(xlUp).Row
xLast_Row = ActiveCell.Row
'Range("A7").Select
If xLast_Row = Cells.Rows.Count Then
MsgBox ("No data found - run cancelled.")
Exit Sub
End If
With Sheets("Risk_Return").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("E7:E" & xLast_Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("A6:E" & xLast_Row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A7") = "1"
Range("A7").AutoFill Destination:=Range("A7:A" & xLast_Row), Type:=xlFillSeries
' ActiveSheet.Protect Password:="pass"
' ActiveSheet.EnableSelectio
End Sub