muhleebbin
Active Member
- Joined
- Sep 30, 2017
- Messages
- 252
- Office Version
- 365
- 2019
- 2016
- 2013
- 2010
- Platform
- Windows
- MacOS
- Mobile
- 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!
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