asked on Tweeking Code to limit sort
EE Pros.;
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").Acti
Range("E6").End(xlDown).Se
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
End Sub
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").Acti
Range("E6").End(xlDown).Se
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
End Sub
Microsoft Excel
Last Comment
Bright01
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Chris thanks for the tip.... I commented out the second definition; still get a Error 400. Any additional ideas?
B.
B.
On which line?
ASKER
Is the sheet Risk_Return hidden?
Step through the code using f8 and see which line triggers the error.
ASKER
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