Strange sort problem

[Ismael]

Hi,

I have a strange problem with a sort macro.

DRJ, has made for a macro that sort more than one column, the macro works fine, but now with the values that I have paste in the range B9:F20000, a strange situation happen. If you run the macro you will see what I?m saying.

So if any of you guys can give me a help, I appreciate

Thanks in advance,

Best regards

Ismael

[Paleo]

Hi Ismael,

I used your worksheet and got no errors, worked just fine for me. Which error are you getting?

[Ismael]

Hi, Paleo

How are you?

In my computer the column doesn't stay sort......

In the attach file you can see how the this stay.

best regards,


Ismael

[CBrine]

Ismael,
The problem isn't caused by the code(as you stated), it's caused by the format of the data. It looks like you are most likely getting this data from another programs export process. These tend to have non standard formats that excel uses. I've added some extra code to resolve your problem. Give it a try an let us know if you have any problems.

HTH
Cal

Sub Sort()
Range("B9:B20000").Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C9:C20000").Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
Range("D9:D20000").Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
Range("E9:E20000").Sort Key1:=Range("E9"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("F9:F20000").Sort Key1:=Range("F9"), Order1:=xlAscending, Header:= _
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

[Ismael]

Hi, CBrine

The code that tou have send me give a message of an error that ypu can see in the attach.

The data that I have paste in the file, is from another excel file, and I made a copy paste special values in the file that you have, so I don't now why this happens.....

If you have any sugestion, please tell me

thanks

Ismael

[CBrine]

Ismael,
That might be just be a version difference(I'm on 2003), just take out the

TrailingMinusNumbers:=True

from both of the new statements and give it a try.

Cal

[Paleo]

Hi Ismael,

I am back. Just reproduced your problem. Now I will try to fix it.

[Paleo]

Hi Ismael,

I think I made it.

[Ismael]

Hi, Carlos

The last file that you have send me give a message of an error when I run the macro, you can see this error in the picture attach.

But if I do what CBrine, has say and delete ", TrailingMinusNumbers:=True " in the code that CBrine have send me everything work nice.

So thanks to you both.

Best Regards,


Ismael

[Paleo]

Hi Ismael,

works fine for me. Which version of excel are you using? I use 2003.

Anyway you know you are always welcome.

[Ismael]

Hi, Paleo


I'am using office 2000, do you thing problem can be from here?

Best Regards,

Ismael

[Ismael]

Hi, sorry to bother again....

but the code that CBrine made, sort the columns one by one, what I want is sort the values by column D then by E, any then for the next, but if I run the code that CBrine what happen is:

for example I have in b9 - 05:00:45, c9 - 06:00:12, d9 - AZ, e9 - 21 and f9 - 1

after run the macro the values doesn't math.....

So if you guys can see that, I appreciate


Best Regards,

Ismael

[CBrine]

Ismael,
That's the code that DRJ had put together originaly for you. I was wondering how that would help you, since all your values would be misaligned after the process was executed. I figured there must be a reason for this, so didn't comment on it. Let me take a look at the data, and we will see what we can do.

1. You want to sort ALL Columns by the data in Column D.
2. Next sort all data by Column E.
3. Next sort all the data by Column F.

Let me know if this is a true statement.

Cal

[Ismael]

Hi CBrine,

Sorry my mistake.

Yes I want to sort the data first by column D, then by column E, and the rest doesn't matter, the only sting is that in the end all values have to match as the are before the sort.

I hope this help you to solve the problem.

Thanks in advance

best regards,

Ismael

[CBrine]

Ismael,
Here's the code modified to do the sort on D and E and F. Key3 won't make a difference.

Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 2)
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1)
Range("B9:F20000").sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")

HTH
Cal

[Ismael]

Hi CBrine,


Thank you very much, now the macro does extacly are I want.

best regards,

Ismael

[Paleo]

Hi Ismael,

glad you got it working. Just one doubt about this line:

Range("B9:F20000").sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")

Shouldnt it use range B8 till F20000 instead of B9? At least for me there is a blank row row 8).Should it be like this?

Range("B8:F20000").sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")

And what about all that color formated cells from row 131 to row 28259? Shouldnt they be deleted?

Shouldnt the code be?

Option Explicit
Sub Sort()
    Dim FLin As String
Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 2)
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1)
Range("B8:F20000").Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")
Range("d7").End(xlDown).Select
    With Selection
        .Offset(1, 0).Select
        FLin = .Row
    End With
    Rows(FLin & ":65536").EntireRow.Delete
    Range("B7").Select
End Sub

[Ismael]

Hi Paleo,

Yes, I could use the range B8 till F20000 instead of B9 till F20000, but I like to stay 1 empty column between the title (range B7:F7) and the data.

When I deleted the colour formatted in the cells from row 131 to row 28259, I only have a problem that is the data in the last row disappear, in the data that you have the row that contain this data ( 7:00:00, 7:07:27, YS, 9622) will disappear when you run the macro.

I try to add more data to the file and I check that this always happen, I just don't understand why?

Thanks for your help


Best regards,


Ismael


PS: Muito obrigado pela disponibilidade que tens demonstrado
Um grande abra

[Paleo]

Hi Ismael,

problem solved, sorry for the goofed. The new code is:

Option Explicit 
Sub Sort() 
    Dim FLin As String 
Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ 
    :=Array(1, 2) 
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ 
    :=Array(1, 1) 
Range("B9:F20000").Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9") 
Range("d9").End(xlDown).Select 
    With Selection 
        .Offset(1, 0).Select 
        FLin = .Row + 1
    End With 
    Rows(FLin & ":65536").EntireRow.Delete 
    Range("B7").Select 
End Sub

P.S.: Pode contar sempre comigo e quando puder visite o site na minha assinatura. Sou o administrador dele e l? temos uma ?rea para d?vidas em portugu?s.

[Ismael]

Hi, Carlos

Once more thanks for the help.

I already put the new code.

Best Regards,

Ismael


PS: J? fui ao teu site e j? fiz o registo, penso em breve visita-lo com maior frequ?ncia, e quem sabe poder ajudar nas quest?es, pois tb n?o sou grande expert em EXCEL, mas tenciono l? chegar.

Um abra