macro to rank a salaries descending

bearcub

Well-known Member
Joined
May 18, 2005
Messages
613
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

bearcub

Well-known Member
Joined
May 18, 2005
Messages
613
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,815
Office Version
365
Platform
Windows
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.
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
613
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,815
Office Version
365
Platform
Windows
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/
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
613
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,815
Office Version
365
Platform
Windows
Its in the message window, that is when you hit reply or Post New Thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,287
Messages
5,467,754
Members
406,550
Latest member
miraclewhip

This Week's Hot Topics

Top