Solved

Sorting a column

Posted on 2012-12-27
3
296 Views
Last Modified: 2012-12-28
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
0
Comment
Question by:Bright01
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38725767
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
0
 

Author Closing Comment

by:Bright01
ID: 38726526
Brian,

"Beautiful"!!!  Thank you very much.

B.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38726553
Thanks, Bright01!
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

808 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