Sort alphabetically with Gender also

shimaa01234

shimaa01234

Active Member
Joined
Jun 24, 2014
Messages
446
I want code to arrange the names alphabetically and by type also
With the possibility to choose male or female first
NAMETYPE
AMale
BFemale
CFemale
DMale
EMale
FMale
GMale
HFemale
IFemale
JMale
KFemale
LFemale
MMale
NFemale
OMale

<colgroup><col span="2"></colgroup><tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Change code range to suit.
Female/Male option from msgbox Selection in code.
Code: 
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oSort [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Ans = MsgBox("""Yes"" = Male sort " & Chr(10) & """No"" = Female sort", vbYesNo + vbInformation)
 oSort = IIf(Ans = vbYes, xlDescending, xlAscending)
  ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B16") _
        , SortOn:=xlSortOnValues, Order:=oSort, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    [COLOR="Navy"]With[/COLOR] ActiveSheet.Sort
        .SetRange Range("A1:B16")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Give me an error in this part

ActiveSheet.Sort.SortFields.Clear
Maybe, because I use Office 2003</pre>
 
Upvote 0
When work skip this step
Give me a "Error" in this step also
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B16") _
, SortOn:=xlSortOnValues, Order:=oSort, DataOption:=xlSortNormal
 
Upvote 0
Try this for 2003:-
Code: 
Private Sub CommandButton1_Click()
 Dim Ans As String
 Dim oSort As String
 Ans = MsgBox("""No"" = Male sort" & Chr(10) & """Yes"" = Female sort", vbYesNo + vbInformation)
 oSort = IIf(Ans = vbYes, xlAscending, xlDescending)
 Range("A2:B16").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:B16").Select
    Selection.Sort Key1:=Range("B2"), Order1:=oSort, Key2:=Range("A2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End Sub
 
Upvote 0
You're welcome.

NB:- 90% of that code was straight from the Macro recorder, something that you might be able to use to your advantage in the future.
 
Upvote 0
Thank you for the wonderful advice
If there are other columns, such as this table
How is the ranking?
NAMEReligionCLASSTYPE
AWW1Male
BWW2Female
CWW3Female
DWW1Male
EWW2Male
FWW3Male
GWW2Male
HWW3Female
IWW4Female
JWW1Male
KWW3Female
LWW5Female
MWW1Male
NWW2Female
OWW3Male

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
Try this:-
Code: 
Private Sub CommandButton1_Click()
 Dim Ans As String
 Dim oSort As String
 Ans = MsgBox("""No"" = Male sort" & Chr(10) & """Yes"" = Female sort", vbYesNo + vbInformation)
 oSort = IIf(Ans = vbYes, xlAscending, xlDescending)
 Range("A2:D16").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:D16").Select
    Selection.Sort Key1:=Range("D2"), Order1:=oSort, Key2:=Range("A2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Change gender and modify some words accordingly.
Replies
5
Views
327
harzer
H
S
  • Question
Database, VBA
Replies
0
Views
193
Sefty
S
GMC The Macro Man
  • Solved
Filter rows that meet criteria
Replies
6
Views
238
GMC The Macro Man
GMC The Macro Man
M
  • Solved
Search by textbox and criteria combobox and show in listbox
2
Replies
10
Views
432
DanteAmor
DanteAmor
GMC The Macro Man
  • Question
Filter columns with criteria
2
Replies
10
Views
403
Fluff
Fluff

Forum statistics

Threads
1,214,606
Messages
6,120,488
Members
448,967
Latest member
visheshkotha

Share this page

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