Improve company productivity with a Business Account.Sign Up

x
?
Solved

Tweeking Code to limit sort

Posted on 2013-01-02
9
Medium Priority
?
227 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

587 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