Arrange numbers small to big in ascending order

[motilulla]

Using Excel 2010

Hello,

I have number in columns D:H I want a macro which can make them in ascending order from n1 through n5 small to bigger number or A to Z

Note: I want a macro which can be used in Excel 2000 that has only 3 fileds to make them in order. But here are the 5 fileds.

Here is the descending order data example.

Excel Question ExcelFourms.xls
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				5	6	7	8	9
7				4	6	7	8	10
8				4	5	7	9	10
9				3	6	7	9	10
10				3	5	8	9	10
11				2	6	8	9	10
12				1	7	8	9	10
13				4	5	7	8	11
14				3	6	7	8	11
15				4	5	6	9	11
16				3	5	7	9	11
17				2	6	7	9	11
18				3	4	8	9	11
19				2	5	8	9	11
20				1	6	8	9	11
21				3	5	6	10	11
22				3	4	7	10	11
23				2	5	7	10	11
24				1	6	7	10	11
25				2	4	8	10	11
26				1	5	8	10	11
27				2	3	9	10	11
28				1	4	9	10	11
29				4	5	6	8	12
30				3	5	7	8	12
31				2	6	7	8	12
32				3	5	6	9	12
33				3	4	7	9	12
34				2	5	7	9	12
35				1	6	7	9	12
36				2	4	8	9	12
37				1	5	8	9	12
38				3	4	6	10	12
39				2	5	6	10	12
40				2	4	7	10	12
41				1	5	7	10	12
42				2	3	8	10	12
43				1	4	8	10	12
44				1	3	9	10	12
45				3	4	5	11	12
46				2	4	6	11	12
47				1	5	6	11	12
48				2	3	7	11	12
49				1	4	7	11	12
50				1	3	8	11	12
51				1	2	9	11	12
52				4	5	6	7	13
53				3	5	6	8	13
54				3	4	7	8	13
55				2	5	7	8	13
56				1	6	7	8	13
57				3	4	6	9	13
58				2	5	6	9	13
59				2	4	7	9	13
60				1	5	7	9	13
61				2	3	8	9	13
62				1	4	8	9	13
63				3	4	5	10	13
64				2	4	6	10	13
65				1	5	6	10	13
66				2	3	7	10	13
67				1	4	7	10	13
68				1	3	8	10	13
69				1	2	9	10	13
70				2	4	5	11	13
71				2	3	6	11	13
72				1	4	6	11	13
73				1	3	7	11	13
74				1	2	8	11	13
75				2	3	5	12	13
76				1	4	5	12	13
77				1	3	6	12	13
78				1	2	7	12	13
79				3	5	6	7	14
80				3	4	6	8	14
81				2	5	6	8	14
82				2	4	7	8	14
83				1	5	7	8	14
84				3	4	5	9	14
85				2	4	6	9	14
86				1	5	6	9	14
87				2	3	7	9	14
88				1	4	7	9	14
89				1	3	8	9	14
90				2	4	5	10	14
91				2	3	6	10	14
92				1	4	6	10	14
93				1	3	7	10	14
94				1	2	8	10	14
95				2	3	5	11	14
96				1	4	5	11	14
97				1	3	6	11	14
98				1	2	7	11	14
99				2	3	4	12	14
100				1	3	5	12	14
101				1	2	6	12	14
102				1	3	4	13	14
103				1	2	5	13	14
104				3	4	6	7	15
105				2	5	6	7	15
106				3	4	5	8	15
107				2	4	6	8	15
108				1	5	6	8	15
109				2	3	7	8	15
110				1	4	7	8	15
111				2	4	5	9	15
112				2	3	6	9	15
113				1	4	6	9	15
114				1	3	7	9	15
115				1	2	8	9	15
116				2	3	5	10	15
117				1	4	5	10	15
118				1	3	6	10	15
119				1	2	7	10	15
120				2	3	4	11	15
121				1	3	5	11	15
122				1	2	6	11	15
123				1	3	4	12	15
124				1	2	5	12	15
125				1	2	4	13	15
126				1	2	3	14	15
127				3	4	5	7	16
128				2	4	6	7	16
129				1	5	6	7	16
130				2	4	5	8	16
131				2	3	6	8	16
132				1	4	6	8	16
133				1	3	7	8	16
134				2	3	5	9	16
135				1	4	5	9	16
136				1	3	6	9	16
137				1	2	7	9	16
138				2	3	4	10	16
139				1	3	5	10	16
140				1	2	6	10	16
141				1	3	4	11	16
142				1	2	5	11	16
143				1	2	4	12	16
144				1	2	3	13	16
145				3	4	5	6	17
146				2	4	5	7	17
147				2	3	6	7	17
148				1	4	6	7	17
149				2	3	5	8	17
150				1	4	5	8	17
151				1	3	6	8	17
152				1	2	7	8	17
153				2	3	4	9	17
154				1	3	5	9	17
155				1	2	6	9	17
156				1	3	4	10	17
157				1	2	5	10	17
158				1	2	4	11	17
159				1	2	3	12	17
160				2	4	5	6	18
161				2	3	5	7	18
162				1	4	5	7	18
163				1	3	6	7	18
164				2	3	4	8	18
165				1	3	5	8	18
166				1	2	6	8	18
167				1	3	4	9	18
168				1	2	5	9	18
169				1	2	4	10	18
170				1	2	3	11	18
171				2	3	5	6	19
172				1	4	5	6	19
173				2	3	4	7	19
174				1	3	5	7	19
175				1	2	6	7	19
176				1	3	4	8	19
177				1	2	5	8	19
178				1	2	4	9	19
179				1	2	3	10	19
180				2	3	4	6	20
181				1	3	5	6	20
182				1	3	4	7	20
183				1	2	5	7	20
184				1	2	4	8	20
185				1	2	3	9	20
186				2	3	4	5	21
187				1	3	4	6	21
188				1	2	5	6	21
189				1	2	4	7	21
190				1	2	3	8	21
191				1	3	4	5	22
192				1	2	4	6	22
193				1	2	3	7	22
194				1	2	4	5	23
195				1	2	3	6	23
196				1	2	3	5	24
197				1	2	3	4	25
198
199
200
201
202
203
204
Sheet4

Thank you.


Regards,
Moti

 

[DanteAmor]

You are correct that I can do in excel 2010 but we don’t have option in excel 2000 so far need a macro sort them please is it possible?

The macro is in post #9.

I put it again:

Sub Macro4()
  Dim lr As Long

  lr = Range("D" & Rows.Count).End(3).Row
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("D6:D" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("E6:E" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("F6:F" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("G6:G" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("H6:H" & lr), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("D5:H" & lr)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
End Sub

 

[motilulla]

Try this macro:

Sub Macro4()
  Dim lr As Long

  lr = Range("D" & Rows.Count).End(3).Row
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("D6:D" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("E6:E" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("F6:F" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("G6:G" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("H6:H" & lr), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("D5:H" & lr)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
End Sub

@Dante Amor. That is spot on this did the excellent job in a milliseconds.

I appreciate your time and help and sorry for the inconvenient

Have a nice day and good luck!

Kind Regards,
Moti

 

[Peter_SSs]

Note: I want a macro which can be used in Excel 2000 that has only 3 fileds to make them in order.

but we don’t have option in excel 2000 so far need a macro sort them please is it possible?

What happened to the above requirements?
Wouldn't this simple one work in all versions?

Sub SortWith3()
  With Range("D5", Range("D" & Rows.Count).End(xlUp)).Resize(, 5)
    .Sort Key1:=.Columns(4), Order1:=xlAscending, Key2:=.Columns(5), Order2:=xlAscending, Header:=xlYes
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(3), Order3:=xlAscending, Header:=xlYes
  End With
End Sub

 

[motilulla]

What happened to the above requirements?
Wouldn't this simple one work in all versions?

Sub SortWith3()
  With Range("D5", Range("D" & Rows.Count).End(xlUp)).Resize(, 5)
    .Sort Key1:=.Columns(4), Order1:=xlAscending, Key2:=.Columns(5), Order2:=xlAscending, Header:=xlYes
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(3), Order3:=xlAscending, Header:=xlYes
  End With
End Sub

Hello Peter_SSs,

No doubt this version is far better I will go with this universal for all version option.

Thank you for your help and time for giving another choice.

Have a nice day and good luck!

Kind Regards,
Moti

 

[Peter_SSs]

You're welcome. Glad to contribute. Thanks for the follow-up.