macro to rank a salaries descending

[bearcub]

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

 

[bearcub]

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?

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

 word but I'm not sure if I need to an any "/"'s to it.

 

[Fluff]

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]

Okay, thank you so. So I would post the code with the

 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]

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]

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]

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

 

[bearcub]

Thank you , that clears that up