Simple MultiColumn Sorting

J

Jaemun

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.
 
D

Debra Dalgleish

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

Jaemun

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
 
D

Debra Dalgleish

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?
 
J

Jaemun

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
 
D

Debra Dalgleish

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.
 
J

Jaemun

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

Debra Dalgleish

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.
 
J

Jaemun

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Runtime error 1004 Application-defined or object defined error" on data sort 0
Sorting columns of data 1
Tidying Recorded Macro Code 11
Onclick 2 events 2
Simple Do... Until problem 4
Sort Errors 4
Simplfying the Sort macro 2
Sorting a range on another worksheet without selecting the worksheet 1

Top