Sorting producing wrong result

[nkechifesie]

I have an excel sheet containing some indictators which I sort to help me analyse my system. After running the macro I discovered that some columns no longer have the right values , like it didnt sort well, probably sort just one column without carrying the other columns related to it.
e.g, lets say I have these columns and i want to sort by age DESCENDING

NAME SURNAME AGE
JACK JILL 20
PETER PAUL 21

It gives me
NAME SURNAME AGE
PETER JILL 21
JACK PAUL 20

this is a part of the code and after the sort is where the problem occurs.

Sheets("No Counters").Rows(1).Value = Sheets("Matrix sheet").Rows(1).Value
    Sheets("Matrix sheet").Select
    Columns("E:E").Select
    Range("A1:CN" & Cells(Rows.Count, 1).End(xlDown).Row).Sort key1:=Range("E1"), order1:=xlDescending, header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

thanks for your help

[Killer42]

Haven't done a lot of work with Excel myself, but I do have a question for you. What happens if you leave out the Select of column E (comment out line 3)?

[nkechifesie]

Haven't done a lot of work with Excel myself, but I do have a question for you. What happens if you leave out the Select of column E (comment out line 3)?

Did that but still no change

[Killer42]

Did that but still no change

Hm... interesting.

I'm using Excel 2000 (had to comment out DataOption1 because this old version didn't recognise xlSortNormal) and it seems to do a very poor job of guessing the headers. It might be worth trying xlYes rather than xlGuess.

But beyond that, I'm mystified. Still, a bit of standard debugging practice might help...

I suppose the first thing to try (you may have already done this) is to tell Excel to record a new macro, and just do the sort yourself. Then test whether executing the newly-recorded macro works correctly. If so, then you just need to identify what's different.

Oh, one other thought which comes to mind. Do you have non-contiguous ranges that have to be included? In other words, do you have gaps that will stuff up the sort? If not, instead of A1:CNx I'd try setting the range to be sorted to simply A1. Excel usually seems to do a pretty good job of choosing the range to be sorted. At least it did in my case, but I didn't have your actual data to test with.

[nkechifesie]

[resolved]

Thanks so very much for all your help. I discovered where the problem was coming from. in the range for the sort, i didnt select all the columns which had values, i stopped at "CN" instead of "DJ". I have decided to avoid any future errors i would select it up to the last column on the sheet which is "IV".
This is the working code now

 Sheets("No Counters").Rows(1).Value = Sheets("Matrix sheet").Rows(1).Value
    Sheets("Matrix sheet").Select
    'Columns("E:E").Select
    Range("A1:[COLOR="Red"]IV[/COLOR]" & Cells(Rows.Count, 1).End(xlDown).Row).Sort key1:=Range("E1"), order1:=xlDescending, header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

[Killer42]

Glad to hear you've managed to resolve it.

One question, though. Did you try specifying a range of just "A1", to see what happens? It worked in my little test, but the layout of your data may not suit it.

The thing is, if this works it would prevent (a) the problem you reported here, and (b) the need to expand the range if further columns are added. (Although after re-reading I see you've gotten around (b) by selecting all the columns.