macro to rank a salaries descending

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have a sheet in a file where I have potential salaries by district office. I would like to sort this list automatically by district (there might be 5, 10, or 15 offices in one district. I have multiple ranges to rank on this sheet).

I was thinking about creating a helpful column using the rank function to rank the values then use a macro to create a descending sort. I would have many ranges to short because the list is divided into separate categories that will have different sort orders. My macro would include 15 or 20 non-contiguous ranges to sort independently of each

I would have to have a button or a macro to make this happen. I would have to hide the columns from view as well. This file would be going out to various users who may not really be Excel savvy so I might have some explaining to do.

Then I thought maybe I could use one of the worksheet events to trigger the macro without the use of helper column. Is this possible? What would the code look like?

Thank you for your help,

Michael
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I did find the following macro online that seems to do the trick. I have to sort by different ranges as you can see by the below code. Would there be a more efficient way of writing this code using some sort of loop?


Code:
Public Sub sbSortDataInExcel()
  Dim strDataRange As Range
  Dim keyRange As Range

Set strDataRange = Range("A2:B17")
Set keyRange = Range("B2")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending

Set strDataRange = Range("C2:D17")
Set keyRange = Range("D2")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending

Set strDataRange = Range("E2:F17")
Set keyRange = Range("F2")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending

Set strDataRange = Range("G2:H17")
Set keyRange = Range("H2")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending

Set strDataRange = Range("I2:J17")
Set keyRange = Range("J2")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending

Set strDataRange = Range("K2:L17")
Set keyRange = Range("L2")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending
End Sub




Michael

PS Also, how to I portray VBA code. I know I have to use the
Code:
 word but I'm not sure if I need to an any "/"'s to it.
 
Last edited by a moderator:
Upvote 0
To use code tags simply click the # icon in the message window (this will put the code tags in) & then paste the code between the tags.
 
Upvote 0
Okay, thank you so. So I would post the code with the
Code:
 tags attached then press the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=icon]#icon[/URL]  (your message was [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] ) and it apply the code?
 
Upvote 0
Either paste the code into the message window, select all the code & click the # icon, or click the # icon & then paste the code between the tags that appear. You do not need to add the tags manually.
Give it a try on the test board https://www.mrexcel.com/forum/test-here/
 
Upvote 0
When I create a new message I don't see the icon on my message though I see #5 on yours - this one would be #6 ? Do I have to turn something one because I don't see anywhere in this message that I'm writing now in the top right corner.

Sorry to be a nuance.

Michael
 
Upvote 0
Its in the message window, that is when you hit reply or Post New Thread.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top