• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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").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

Range("A7") = "1"
Range("A7").AutoFill Destination:=Range("A7:A" & xLast_Row), Type:=xlFillSeries


End Sub
0
Bright01
Asked:
Bright01
  • 4
  • 3
  • 2
2 Solutions
 
Rory ArchibaldCommented:
If there is no other data in column A and the blanks are at the bottom of the real data, replace
Range("E6").End(xlDown).Select

xLast_Row = ActiveCell.Row

Open in new window

with
xLast_Row = cells(rows.count, "A").End(xlup).row

Open in new window

0
 
Bright01Author Commented:
Thanks!  I'm getting an Errorcode 400.

Here's what I have now;

Option Explicit

Sub Prioritise()
Dim xLast_Row As Long

'ActiveSheet.Unprotect Password:="pass"

Sheets("Risk_Return").Activate

'Range("E6").End(xlDown).Select
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.EnableSelection = xlUnlockedCells

End Sub
0
 
ChrisKaderCommented:
xLast_Row is definded two times.

'Range("E6").End(xlDown).Select
xLast_Row = Cells(Rows.Count, "A").End(xlUp).Row
xLast_Row = ActiveCell.Row
'Range("A7").Select

Open in new window


It probably should be

xLast_Row = Cells(Rows.Count, "A").End(xlUp).Row

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bright01Author Commented:
Chris thanks for the tip.... I commented out the second definition; still get a Error 400.  Any additional ideas?

B.
0
 
Rory ArchibaldCommented:
On which line?
0
 
Bright01Author Commented:
Here is the error code; it doesn't show a particular line.

B.
Error-Code-400.png
0
 
ChrisKaderCommented:
Is the sheet Risk_Return hidden?
0
 
Rory ArchibaldCommented:
Step through the code using f8 and see which line triggers the error.
0
 
Bright01Author Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now