Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simple MultiColumn Sorting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default 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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Simple question on Sorting jlacap Excel Discussion (Misc queries) 5 October 5th 08 10:34 AM
automatically sorting a simple list cbguy Excel Discussion (Misc queries) 2 March 23rd 07 06:04 PM
MultiColumn ComboBox Paul Smith[_3_] Excel Programming 2 August 3rd 05 06:36 AM
display multicolumn box Alen32 Excel Programming 1 March 12th 05 05:00 PM
Multicolumn Combo Box PokerDude Excel Programming 2 January 29th 04 04:02 PM


All times are GMT +1. The time now is 02:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement