Why does this sort not work please!

[S.H.A.D.O.]

Good afternoon,

I have the code...

    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("B56:B69"), Order:=xlAscending
    End With
    With ActiveSheet.Sort
        .SetRange Range("B56:M69")
        .Apply
    End With
    Range("O2").Select

The numbers in column B are 1-14. When the code above is run, it puts 14 at the top and then the numbers 1-13 below the 14.

Thanks in advance.

 

[Joe4]

It sounds like it is treating your first row as a header row. You can explicitly tell it to do that (or not), like this:

    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("B56:B69"), Order:=xlAscending
    End With
    With ActiveSheet.Sort
        .SetRange Range("B56:M69")
[COLOR=#ff0000]        .Header = xlNo[/COLOR]
        .Apply
    End With
    Range("O2").Select

 

[S.H.A.D.O.]

Thanks for the reply Joe4, unfortunately that doesn't work. I have the numbers 1-14 custom formatted as 00.When I remove this formatting it works OK!

 

[S.H.A.D.O.]

Not to worry, I have sorted it out!

It was the fact that the numbers 1-13 were some how formatted slightly different to the number 14 [although retained the 00 formatting!]. I just copied the number 14 down and then manually input the numbers 1-13. I then ran the Macro and it worked.

Thanks for your help Joe4, it is appreciated.

 

[Joe4]

I have a strong suspicion I know what the problem was. Custom formatting only applies to numbers.
I am guessing that some of your entries were entered as numbers, and some with entered as text. Those will sort differently (numbers are typically sorted before text).

 

[S.H.A.D.O.]

Hi Joe4,

I have a strong suspicion I know what the problem was. Custom formatting only applies to numbers.
I am guessing that some of your entries were entered as numbers, and some with entered as text. Those will sort differently (numbers are typically sorted before text).

I am absolutely sure that's what it was.

Thanks again, it is appreciated.

 

[Joe4]

You are welcome.
Glad you have it working!