Solved

Tweeking Code to limit sort

Posted on 2013-01-02
9
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 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 200 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
Technology Partners: 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!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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