Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Tweeking Code to limit sort

Posted on 2013-01-02
9
Medium Priority
?
221 Views
Last Modified: 2013-01-02
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
Comment
Question by:Bright01
  • 4
  • 3
  • 2
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1200 total points
ID: 38736431
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
 

Author Comment

by:Bright01
ID: 38736500
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
 

Assisted Solution

by:ChrisKader
ChrisKader earned 800 total points
ID: 38736524
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Bright01
ID: 38736583
Chris thanks for the tip.... I commented out the second definition; still get a Error 400.  Any additional ideas?

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38736595
On which line?
0
 

Author Comment

by:Bright01
ID: 38736610
Here is the error code; it doesn't show a particular line.

B.
Error-Code-400.png
0
 

Expert Comment

by:ChrisKader
ID: 38736619
Is the sheet Risk_Return hidden?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38736673
Step through the code using f8 and see which line triggers the error.
0
 

Author Closing Comment

by:Bright01
ID: 38736854
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question