ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Simple MultiColumn Sorting (https://www.excelbanter.com/excel-programming/348363-simple-multicolumn-sorting.html)

Jaemun
Simple MultiColumn Sorting
 
Hello ppl,

Please help to simplify my following macros. The sorting event should be in
order and
must not be loop.

Sub MultiColumn_Sorting()
Application.screenupdating = false
Range("A3:U52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

End Sub

Thanks in advance.



Debra Dalgleish
Simple MultiColumn Sorting
 
What are you trying to accomplish? Each sort refers to cell X3, that
isn't in the selected range.

Jaemun wrote:
Hello ppl,

Please help to simplify my following macros. The sorting event should be in
order and
must not be loop.

Sub MultiColumn_Sorting()
Application.screenupdating = false
Range("A3:U52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

End Sub

Thanks in advance.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Jaemun
Simple MultiColumn Sorting
 
Hi Debra Dalgleish,
First of all, many thanks for your respond.
There was a mistake in the range given which is suppose to be as follows:-

Sub MultiColumn_Sorting()
Application.screenupdating = false

' ----------Corrected----------
Range("A3:X52").Select

' ----------Corrected----------

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

End Sub

Thank you again.
Jaemun


"Debra Dalgleish" wrote in message
...
What are you trying to accomplish? Each sort refers to cell X3, that isn't
in the selected range.

Jaemun wrote:
Hello ppl,

Please help to simplify my following macros. The sorting event should be
in order and
must not be loop.

Sub MultiColumn_Sorting()
Application.screenupdating = false
Range("A3:U52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

End Sub

Thanks in advance.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish
Simple MultiColumn Sorting
 
For someone to help you simplify the code, you still need to explain
what you are trying to accomplish. The same columns seem to be
repeatedly sorted in the code, which is confusing.
What sort order do you need in the final result?

Jaemun wrote:
Hi Debra Dalgleish,
First of all, many thanks for your respond.
There was a mistake in the range given which is suppose to be as follows:-

Sub MultiColumn_Sorting()
Application.screenupdating = false

' ----------Corrected----------
Range("A3:X52").Select

' ----------Corrected----------

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

End Sub

Thank you again.
Jaemun


"Debra Dalgleish" wrote in message
...

What are you trying to accomplish? Each sort refers to cell X3, that isn't
in the selected range.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Jaemun
Simple MultiColumn Sorting
 
Hi again,

The effected area should be within range("A3:X52"). But the main target
column to sort the area is Column "X" while Column "J,K,L,M,N,O,P,Q,R,A" are
the next sort order (This is the rules).

The following are an exact example that the macro should accomplished the
sort order.

A B C J K L M N O P
Q R X

2 01 James 6 5 5 5 5 5
5 5 5 46
2 02 David 5 6 5 5 5 5
5 5 5 46
2 03 Harry 5 5 6 5 5 5
5 5 5 46
2 04 Steve 5 5 5 6 5 5
5 5 5 46
2 05 Peter 5 5 5 5 6 5
5 5 5 46
2 06 Jack 5 5 5 5 5 6
5 5 5 46
2 07 Jacky 5 5 5 5 5 5
6 5 5 46
2 08 Philip 5 5 5 5 5 5
5 6 5 46
2 09 Mike 5 5 5 5 5 5
5 5 6 46
5 10 George 5 5 5 5 5 5
5 5 5 45
3 11 Garry 5 5 5 5 5 5
5 5 5 45
2 12 Kent 5 5 5 5 5 5
5 5 5 45

To do that kind of job, I've recorded a macros and then manipulated them as
in the
following sub:-

Sub MultiColumn_Sorting()
Application.ScreenUpdating = False
Range("A3:X52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Debra Dalgleish
Simple MultiColumn Sorting
 
You can sort the columns in four groups, in order of least importance.

First, sort QRA, then NOP, then KLM, then XJ.

Use the macro recorder to create the code as you do the sort manually.

Jaemun wrote:
Hi again,

The effected area should be within range("A3:X52"). But the main target
column to sort the area is Column "X" while Column "J,K,L,M,N,O,P,Q,R,A" are
the next sort order (This is the rules).

The following are an exact example that the macro should accomplished the
sort order.

A B C J K L M N O P
Q R X

2 01 James 6 5 5 5 5 5
5 5 5 46
2 02 David 5 6 5 5 5 5
5 5 5 46
2 03 Harry 5 5 6 5 5 5
5 5 5 46
2 04 Steve 5 5 5 6 5 5
5 5 5 46
2 05 Peter 5 5 5 5 6 5
5 5 5 46
2 06 Jack 5 5 5 5 5 6
5 5 5 46
2 07 Jacky 5 5 5 5 5 5
6 5 5 46
2 08 Philip 5 5 5 5 5 5
5 6 5 46
2 09 Mike 5 5 5 5 5 5
5 5 6 46
5 10 George 5 5 5 5 5 5
5 5 5 45
3 11 Garry 5 5 5 5 5 5
5 5 5 45
2 12 Kent 5 5 5 5 5 5
5 5 5 45

To do that kind of job, I've recorded a macros and then manipulated them as
in the
following sub:-

Sub MultiColumn_Sorting()
Application.ScreenUpdating = False
Range("A3:X52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Jaemun
Simple MultiColumn Sorting
 
Is there any other way to simplify the codes to make is shorter like
repeating the event or whatever (I have no idea how)?


"Debra Dalgleish" wrote in message
...
You can sort the columns in four groups, in order of least importance.

First, sort QRA, then NOP, then KLM, then XJ.

Use the macro recorder to create the code as you do the sort manually.

Jaemun wrote:
Hi again,

The effected area should be within range("A3:X52"). But the main target
column to sort the area is Column "X" while Column "J,K,L,M,N,O,P,Q,R,A"
are
the next sort order (This is the rules).

The following are an exact example that the macro should accomplished
the
sort order.

A B C J K L M N O
P
Q R X

2 01 James 6 5 5 5 5 5
5 5 5 46
2 02 David 5 6 5 5 5 5
5 5 5 46
2 03 Harry 5 5 6 5 5
5
5 5 5 46
2 04 Steve 5 5 5 6 5
5
5 5 5 46
2 05 Peter 5 5 5 5 6
5
5 5 5 46
2 06 Jack 5 5 5 5 5
6
5 5 5 46
2 07 Jacky 5 5 5 5 5
5
6 5 5 46
2 08 Philip 5 5 5 5 5
5
5 6 5 46
2 09 Mike 5 5 5 5 5
5
5 5 6 46
5 10 George 5 5 5 5 5 5
5 5 5 45
3 11 Garry 5 5 5 5 5
5
5 5 5 45
2 12 Kent 5 5 5 5 5
5
5 5 5 45

To do that kind of job, I've recorded a macros and then manipulated them
as
in the
following sub:-

Sub MultiColumn_Sorting()
Application.ScreenUpdating = False
Range("A3:X52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish
Simple MultiColumn Sorting
 
You could use a With -- End With for the range, but that wouldn't make
it much shorter or faster. Maybe your teacher could help.

Jaemun wrote:
Is there any other way to simplify the codes to make is shorter like
repeating the event or whatever (I have no idea how)?


"Debra Dalgleish" wrote in message
...

You can sort the columns in four groups, in order of least importance.

First, sort QRA, then NOP, then KLM, then XJ.

Use the macro recorder to create the code as you do the sort manually.

Jaemun wrote:

Hi again,

The effected area should be within range("A3:X52"). But the main target
column to sort the area is Column "X" while Column "J,K,L,M,N,O,P,Q,R,A"

are

the next sort order (This is the rules).

The following are an exact example that the macro should accomplished

the

sort order.

A B C J K L M N O

P

Q R X

2 01 James 6 5 5 5 5 5
5 5 5 46
2 02 David 5 6 5 5 5 5
5 5 5 46
2 03 Harry 5 5 6 5 5

5

5 5 5 46
2 04 Steve 5 5 5 6 5

5

5 5 5 46
2 05 Peter 5 5 5 5 6

5

5 5 5 46
2 06 Jack 5 5 5 5 5

6

5 5 5 46
2 07 Jacky 5 5 5 5 5

5

6 5 5 46
2 08 Philip 5 5 5 5 5

5

5 6 5 46
2 09 Mike 5 5 5 5 5

5

5 5 6 46
5 10 George 5 5 5 5 5 5
5 5 5 45
3 11 Garry 5 5 5 5 5

5

5 5 5 45
2 12 Kent 5 5 5 5 5

5

5 5 5 45

To do that kind of job, I've recorded a macros and then manipulated them

as

in the
following sub:-

Sub MultiColumn_Sorting()
Application.ScreenUpdating = False
Range("A3:X52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Jaemun
Simple MultiColumn Sorting
 
Thanks for the help Debra,

I guest what I'm up to is not really neccessary, but, I'm very happy and
appreciate your help with your suggestions which is much simple compare to
mine.

Thanks again,

"Debra Dalgleish" wrote in message
...
You could use a With -- End With for the range, but that wouldn't make
it much shorter or faster. Maybe your teacher could help.

Jaemun wrote:
Is there any other way to simplify the codes to make is shorter like
repeating the event or whatever (I have no idea how)?


"Debra Dalgleish" wrote in message
...

You can sort the columns in four groups, in order of least importance.

First, sort QRA, then NOP, then KLM, then XJ.

Use the macro recorder to create the code as you do the sort manually.

Jaemun wrote:

Hi again,

The effected area should be within range("A3:X52"). But the main
target
column to sort the area is Column "X" while Column
"J,K,L,M,N,O,P,Q,R,A"

are

the next sort order (This is the rules).

The following are an exact example that the macro should accomplished

the

sort order.

A B C J K L M N O

P

Q R X

2 01 James 6 5 5 5 5
5
5 5 5 46
2 02 David 5 6 5 5 5
5
5 5 5 46
2 03 Harry 5 5 6 5 5

5

5 5 5 46
2 04 Steve 5 5 5 6 5

5

5 5 5 46
2 05 Peter 5 5 5 5 6

5

5 5 5 46
2 06 Jack 5 5 5 5 5

6

5 5 5 46
2 07 Jacky 5 5 5 5 5

5

6 5 5 46
2 08 Philip 5 5 5 5 5

5

5 6 5 46
2 09 Mike 5 5 5 5 5

5

5 5 6 46
5 10 George 5 5 5 5 5
5
5 5 5 45
3 11 Garry 5 5 5 5 5

5

5 5 5 45
2 12 Kent 5 5 5 5 5

5

5 5 5 45

To do that kind of job, I've recorded a macros and then manipulated
them

as

in the
following sub:-

Sub MultiColumn_Sorting()
Application.ScreenUpdating = False
Range("A3:X52").Select

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("A3"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("Q3"), Order2:=xlDescending, _
Key3:=Range("R3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("P3"), Order2:=xlDescending, _
Key3:=Range("Q3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("O3"), Order2:=xlDescending, _
Key3:=Range("P3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("N3"), Order2:=xlDescending, _
Key3:=Range("O3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("M3"), Order2:=xlDescending, _
Key3:=Range("N3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("L3"), Order2:=xlDescending, _
Key3:=Range("M3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("K3"), Order2:=xlDescending, _
Key3:=Range("L3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
Key1:=Range("X3"), Order1:=xlDescending, _
Key2:=Range("J3"), Order2:=xlDescending, _
Key3:=Range("K3"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


All times are GMT +1. The time now is 03:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com