Sort by Cell Value VBA

muhleebbin

Active Member
Joined
Sep 30, 2017
Messages
252
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi Everyone,

I have the following sort code but would like it to be controlled by a selection field in cell E1. Currently this automatically sorts by Department, Last Name and then # of Days (hired). Department will always be the first level of the sort however, I'd like to add a selection field in E1 that would then sort it by that respective selection. E1 would contain # of Days, Office Number and Last Name.

Primary issue would be if Last Name is selected there would be no need for the third key in the current code as well as I'd like it to be Ascending vs the Descending in the current code for the second key. Also if Office Number is selected, I'd like that to be Ascending as well. There are 2 people in certain offices so the third key would still be valid.

Thanks in advance for your assistance!

VBA Code:
Sub Sort()

Worksheets("Data").Unprotect
    With ActiveSheet.Sort
        .SetRange Range("A5:AR1001")
        .SortFields.Add Key:=Range("D5"), Order:=xlDescending
        .SortFields.Add Key:=Range("O5"), Order:=xlDescending
        .SortFields.Add Key:=Range("A5"), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
    
Sheets("Data").Sort.SortFields.Clear
        
Worksheets("Data").Protect

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
In the absence of a shorter way you could simply add an IF statement to your code that checks what is in cell E! and then sorts based on the cell value
eg IF e1 = *** then sort this way
else if e1 = !!! then sort another way
 
Upvote 0
Solution
Thank you very much for the suggestion! Sometimes I tend to overthink things but I was able to get it to work.
 
Upvote 0

Forum statistics

Threads
1,223,943
Messages
6,175,547
Members
452,652
Latest member
eduedu

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