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

[rory]

Hi,
Are there any formulas in the cells you have a problem with? Can you post a sample workbook that shows the problem?
Regards,
Rory

[JimmyTheHand]

Hi

I'm not sure what the problem is, but this range definition
Range("A1:CN" & Cells(Rows.Count, 1).End(xlDown).Row) surely looks weird. What do you mean to achive by it?

Jimmy

[nkechifesie]

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:IV" & Cells(Rows.Count, 1).End(xlDown).Row).Sort key1:=Range("E1"), order1:=xlDescending, header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

[rory]

Assuming your column A has data for every row, your code should be something like:
Range("A1:IV" & Cells(Rows.Count, 1).End(xlUp).Row).Sort key1:=Range("E1"), order1:=xlDescending, header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal