Sort with values on top
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a spreadsheet that lists a Facility, Housing Area, and a person's last name.
There are about 8 facilities. I want to sort by a specific facility and place on top to the top of the spreadsheet.
For example, say I have 100 rows where each row indicates a certain facility. Choose Queens Facility as a cell sorting criteria, and there are 10 rows with Queens Facility. I would like to sort all Queens Facility rows to be the first 10.
Here is the code. What is the other criteria to put a specific chosen facility on the top?
Sub SSSsort()
Dim lngRows As Long
lngRows = Range("AD65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("DumpTab").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1:A" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("B1:B" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("E1:E" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
.SetRange Range("A1:AD" & lngRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Here is a sample table in EXCEL:
excel vba
edited Dec 24 '18 at 20:57
Community♦
11
asked Nov 16 '18 at 15:50
MikeMike
144
add a comment |
I have a spreadsheet that lists a Facility, Housing Area, and a person's last name.
There are about 8 facilities. I want to sort by a specific facility and place on top to the top of the spreadsheet.
For example, say I have 100 rows where each row indicates a certain facility. Choose Queens Facility as a cell sorting criteria, and there are 10 rows with Queens Facility. I would like to sort all Queens Facility rows to be the first 10.
Here is the code. What is the other criteria to put a specific chosen facility on the top?
Sub SSSsort()
Dim lngRows As Long
lngRows = Range("AD65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("DumpTab").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1:A" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("B1:B" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("E1:E" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
.SetRange Range("A1:AD" & lngRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Here is a sample table in EXCEL:
excel vba
edited Dec 24 '18 at 20:57
Community♦
11
asked Nov 16 '18 at 15:50
MikeMike
144
Note that without access to your data, we are not able to see what column corresponds to what criterion. Regarding your question, if you want the rows grouped by facility, your first sorting criterion needs to be the facility column. This will only sort them alphabetically, though. If you want a different order, you'll need to look into user-defined lists. You could also try recording a macro while manually setting up a sorting with a custom list.
– Inarion
Nov 16 '18 at 16:31
I added the sample data for your review
– Mike
Nov 16 '18 at 16:53
Perhaps a new column wherein you can put a number (through VBA), "0" for the facility you want on top, and "1" for everyone else. Then your sort is that column, then the facility.
– JNevill
Nov 16 '18 at 16:53
The macro recorder should solve all your problems. Record yourself using the Excel Sort feature then look at the code it generated.
– nicomp
Nov 16 '18 at 16:55
add a comment |
I have a spreadsheet that lists a Facility, Housing Area, and a person's last name.
There are about 8 facilities. I want to sort by a specific facility and place on top to the top of the spreadsheet.
For example, say I have 100 rows where each row indicates a certain facility. Choose Queens Facility as a cell sorting criteria, and there are 10 rows with Queens Facility. I would like to sort all Queens Facility rows to be the first 10.
Here is the code. What is the other criteria to put a specific chosen facility on the top?
Sub SSSsort()
Dim lngRows As Long
lngRows = Range("AD65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("DumpTab").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1:A" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("B1:B" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("E1:E" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
.SetRange Range("A1:AD" & lngRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Here is a sample table in EXCEL:
excel vba
edited Dec 24 '18 at 20:57
Community♦
11
asked Nov 16 '18 at 15:50
MikeMike
144
I have a spreadsheet that lists a Facility, Housing Area, and a person's last name.
There are about 8 facilities. I want to sort by a specific facility and place on top to the top of the spreadsheet.
For example, say I have 100 rows where each row indicates a certain facility. Choose Queens Facility as a cell sorting criteria, and there are 10 rows with Queens Facility. I would like to sort all Queens Facility rows to be the first 10.
Here is the code. What is the other criteria to put a specific chosen facility on the top?
Sub SSSsort()
Dim lngRows As Long
lngRows = Range("AD65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("DumpTab").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1:A" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("B1:B" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("E1:E" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
.SetRange Range("A1:AD" & lngRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Here is a sample table in EXCEL:
excel vba
excel vba
edited Dec 24 '18 at 20:57
Community♦
11
asked Nov 16 '18 at 15:50
MikeMike
144
edited Dec 24 '18 at 20:57
Community♦
11
asked Nov 16 '18 at 15:50
MikeMike
144
edited Dec 24 '18 at 20:57
Community♦
11
edited Dec 24 '18 at 20:57
Community♦
11
edited Dec 24 '18 at 20:57
Community♦
11
11
asked Nov 16 '18 at 15:50
MikeMike
144
asked Nov 16 '18 at 15:50
MikeMike
144
asked Nov 16 '18 at 15:50
MikeMike
144
144
Note that without access to your data, we are not able to see what column corresponds to what criterion. Regarding your question, if you want the rows grouped by facility, your first sorting criterion needs to be the facility column. This will only sort them alphabetically, though. If you want a different order, you'll need to look into user-defined lists. You could also try recording a macro while manually setting up a sorting with a custom list.
– Inarion
Nov 16 '18 at 16:31
I added the sample data for your review
– Mike
Nov 16 '18 at 16:53
Perhaps a new column wherein you can put a number (through VBA), "0" for the facility you want on top, and "1" for everyone else. Then your sort is that column, then the facility.
– JNevill
Nov 16 '18 at 16:53
The macro recorder should solve all your problems. Record yourself using the Excel Sort feature then look at the code it generated.
– nicomp
Nov 16 '18 at 16:55
add a comment |
Note that without access to your data, we are not able to see what column corresponds to what criterion. Regarding your question, if you want the rows grouped by facility, your first sorting criterion needs to be the facility column. This will only sort them alphabetically, though. If you want a different order, you'll need to look into user-defined lists. You could also try recording a macro while manually setting up a sorting with a custom list.
– Inarion
Nov 16 '18 at 16:31
I added the sample data for your review
– Mike
Nov 16 '18 at 16:53
Perhaps a new column wherein you can put a number (through VBA), "0" for the facility you want on top, and "1" for everyone else. Then your sort is that column, then the facility.
– JNevill
Nov 16 '18 at 16:53
The macro recorder should solve all your problems. Record yourself using the Excel Sort feature then look at the code it generated.
– nicomp
Nov 16 '18 at 16:55
Note that without access to your data, we are not able to see what column corresponds to what criterion. Regarding your question, if you want the rows grouped by facility, your first sorting criterion needs to be the facility column. This will only sort them alphabetically, though. If you want a different order, you'll need to look into user-defined lists. You could also try recording a macro while manually setting up a sorting with a custom list.
– Inarion
Nov 16 '18 at 16:31
Note that without access to your data, we are not able to see what column corresponds to what criterion. Regarding your question, if you want the rows grouped by facility, your first sorting criterion needs to be the facility column. This will only sort them alphabetically, though. If you want a different order, you'll need to look into user-defined lists. You could also try recording a macro while manually setting up a sorting with a custom list.
– Inarion
Nov 16 '18 at 16:31
I added the sample data for your review
– Mike
Nov 16 '18 at 16:53
I added the sample data for your review
– Mike
Nov 16 '18 at 16:53
Perhaps a new column wherein you can put a number (through VBA), "0" for the facility you want on top, and "1" for everyone else. Then your sort is that column, then the facility.
– JNevill
Nov 16 '18 at 16:53
Perhaps a new column wherein you can put a number (through VBA), "0" for the facility you want on top, and "1" for everyone else. Then your sort is that column, then the facility.
– JNevill
Nov 16 '18 at 16:53
The macro recorder should solve all your problems. Record yourself using the Excel Sort feature then look at the code it generated.
– nicomp
Nov 16 '18 at 16:55
The macro recorder should solve all your problems. Record yourself using the Excel Sort feature then look at the code it generated.
– nicomp
Nov 16 '18 at 16:55
add a comment |
2 Answers
2
active
oldest
votes
You need to specify the CustomOrder property of the SortFields.Add Method.
For example, if your Facilities were "Jacks Facility", "Queens Facility", "Kings Facility" and "Aces Facility", and the Facility was in Column D, then you could use this code to sort in that order:
.SortFields.Add Key:=Range("D1:D" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="Jacks Facility,Queens Facility,Kings Facility,Aces Facility", _
DataOption:=xlSortNormal
(The Join function will make it easy to collapse an array into a text list, if needed)
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
add a comment |
I figured it out by recording a macro:
'Filter Criteria based on Chosen Facility:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=1, Criteria1:=Facility
'Sort based on housing unit:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=2, Criteria1:=HousingUnit
All you need to do is add the two lines together and you sort based on 2 criteria.
edited Nov 17 '18 at 6:40
Inarion
473312
answered Nov 16 '18 at 18:20
MikeMike
144
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53341251%2fsort-with-values-on-top%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to specify the CustomOrder property of the SortFields.Add Method.
For example, if your Facilities were "Jacks Facility", "Queens Facility", "Kings Facility" and "Aces Facility", and the Facility was in Column D, then you could use this code to sort in that order:
.SortFields.Add Key:=Range("D1:D" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="Jacks Facility,Queens Facility,Kings Facility,Aces Facility", _
DataOption:=xlSortNormal
(The Join function will make it easy to collapse an array into a text list, if needed)
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
add a comment |
You need to specify the CustomOrder property of the SortFields.Add Method.
For example, if your Facilities were "Jacks Facility", "Queens Facility", "Kings Facility" and "Aces Facility", and the Facility was in Column D, then you could use this code to sort in that order:
.SortFields.Add Key:=Range("D1:D" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="Jacks Facility,Queens Facility,Kings Facility,Aces Facility", _
DataOption:=xlSortNormal
(The Join function will make it easy to collapse an array into a text list, if needed)
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
add a comment |
You need to specify the CustomOrder property of the SortFields.Add Method.
For example, if your Facilities were "Jacks Facility", "Queens Facility", "Kings Facility" and "Aces Facility", and the Facility was in Column D, then you could use this code to sort in that order:
.SortFields.Add Key:=Range("D1:D" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="Jacks Facility,Queens Facility,Kings Facility,Aces Facility", _
DataOption:=xlSortNormal
(The Join function will make it easy to collapse an array into a text list, if needed)
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
You need to specify the CustomOrder property of the SortFields.Add Method.
For example, if your Facilities were "Jacks Facility", "Queens Facility", "Kings Facility" and "Aces Facility", and the Facility was in Column D, then you could use this code to sort in that order:
.SortFields.Add Key:=Range("D1:D" & lngRows), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="Jacks Facility,Queens Facility,Kings Facility,Aces Facility", _
DataOption:=xlSortNormal
(The Join function will make it easy to collapse an array into a text list, if needed)
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
answered Nov 16 '18 at 17:01
ChronocidalChronocidal
3,2561318
3,2561318
add a comment |
add a comment |
I figured it out by recording a macro:
'Filter Criteria based on Chosen Facility:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=1, Criteria1:=Facility
'Sort based on housing unit:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=2, Criteria1:=HousingUnit
All you need to do is add the two lines together and you sort based on 2 criteria.
edited Nov 17 '18 at 6:40
Inarion
473312
answered Nov 16 '18 at 18:20
MikeMike
144
add a comment |
I figured it out by recording a macro:
'Filter Criteria based on Chosen Facility:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=1, Criteria1:=Facility
'Sort based on housing unit:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=2, Criteria1:=HousingUnit
All you need to do is add the two lines together and you sort based on 2 criteria.
edited Nov 17 '18 at 6:40
Inarion
473312
answered Nov 16 '18 at 18:20
MikeMike
144
add a comment |
I figured it out by recording a macro:
'Filter Criteria based on Chosen Facility:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=1, Criteria1:=Facility
'Sort based on housing unit:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=2, Criteria1:=HousingUnit
All you need to do is add the two lines together and you sort based on 2 criteria.
edited Nov 17 '18 at 6:40
Inarion
473312
answered Nov 16 '18 at 18:20
MikeMike
144
I figured it out by recording a macro:
'Filter Criteria based on Chosen Facility:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=1, Criteria1:=Facility
'Sort based on housing unit:
Sheets("DumpTab").Range("$A$1:$AX$8228").AutoFilter Field:=2, Criteria1:=HousingUnit
All you need to do is add the two lines together and you sort based on 2 criteria.
edited Nov 17 '18 at 6:40
Inarion
473312
answered Nov 16 '18 at 18:20
MikeMike
144
edited Nov 17 '18 at 6:40
Inarion
473312
edited Nov 17 '18 at 6:40
Inarion
473312
edited Nov 17 '18 at 6:40
Inarion
473312
473312
answered Nov 16 '18 at 18:20
MikeMike
144
answered Nov 16 '18 at 18:20
MikeMike
144
answered Nov 16 '18 at 18:20
MikeMike
144
144
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53341251%2fsort-with-values-on-top%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Note that without access to your data, we are not able to see what column corresponds to what criterion. Regarding your question, if you want the rows grouped by facility, your first sorting criterion needs to be the facility column. This will only sort them alphabetically, though. If you want a different order, you'll need to look into user-defined lists. You could also try recording a macro while manually setting up a sorting with a custom list.
– Inarion
Nov 16 '18 at 16:31
I added the sample data for your review
– Mike
Nov 16 '18 at 16:53
Perhaps a new column wherein you can put a number (through VBA), "0" for the facility you want on top, and "1" for everyone else. Then your sort is that column, then the facility.
– JNevill
Nov 16 '18 at 16:53
The macro recorder should solve all your problems. Record yourself using the Excel Sort feature then look at the code it generated.
– nicomp
Nov 16 '18 at 16:55