Sorting a column

Bright01
Bright01 used Ask the Experts™
on
EE Pros,

I have a simple graphic that depicts priority based on two factors.  I need a macro that looks at a helper cell (column) and determines "priority" (high to low) and properly place a set of sequencial rows in the proper order (highest priority first).  I have attached a worksheet so you can see exactly what I'm trying to accomplish.

Thank you in advance,

B.
Risk-priority-graphic.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
Hi, Bright01.

Please see attached. The code (below) uses the Helper Cells column to determine how many rows are in the table - the last row is considered to be the one before the first blank cell. 
Option Explicit

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

Open in new window

Regards,
Brian.Risk-priority-graphic-V2.xlsm
Bright01

Author

Commented:
Brian,

"Beautiful"!!!  Thank you very much.

B.
redmondb

Commented:
Thanks, Bright01!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial