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
Solved

Tweeking Code to limit sort

Posted on 2013-01-02
9
200 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 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Turn date into age 17 33
Clear a Text Box 7 27
vba to flip column 14 21
Check whether active cell resides in a list (list object) by means of a macro 3 8
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

829 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