Creating xla file to clear junk and sort reqd data
Have a go with this
Sub DelAndSort2()
Range("A:A,C:C,E:F").Delete
' copy values in col-B to col-C
Range("C:C").Value = Range("B:B").Value
' text to columns, "-" separator
Range("C:C").TextToColumns Destination:=Range("C1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("C1") = "Temp" ' ensure there's a header cell
' sort on col C
Columns("A:D").Sort Key1:=Range("C2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C:D").Delete
End Sub
Regards,
Peter T
wrote in message
...
The below code that you gave works well
Sub DelAndSort()
Range("A:A,C:C,E:F").Delete
Columns("A:B").Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
But it does not satisfy my condition. I want my first sort on column
B. I changed the line .Sort Key1:=Range("A2") to "B2" and it still
works fine but if I add few more lines to my data, it does not give
desired output.
For instance, If I add the following to my data
Junk O Junk 10-C Junk Junk
Junk M Junk 10-A Junk Junk
Junk N Junk 10-B Junk Junk
Junk R Junk 10-F Junk Junk
Junk Q Junk 10-E Junk Junk
Junk P Junk 10-D Junk Junk
and make my data range bigger A1:F19 it sorts alphabetically. It does
not recognize numbers and text seperately.
If I run your code changing the sort key1 from A to B it give me the
following result
Code Remark
M 10-A
N 10-B
O 10-C
P 10-D
Q 10-E
R 10-F
A 1-A
B 1-B
C 1-C
D 1-D
E 1-E
F 1-F
G 2-A
H 2-B
I 2-C
J 2-D
K 2-E
L 2-F
But I want it in the following way (column A should not get sorted
first - first sort should always be on column B).
Code Remark
A 1-A
B 1-B
C 1-C
D 1-D
E 1-E
F 1-F
G 2-A
H 2-B
I 2-C
J 2-D
K 2-E
L 2-F
M 10-A
N 10-B
O 10-C
P 10-D
Q 10-E
R 10-F
I hope I made my question clear
|