Sort Errors

[Suzanne]

Help! I can't get a sort to run correctly; both are "sort method of range
class failed";
I'm running this on Excel 2003

This is a macro-based code:

Columns("D").Select
Range("B1:U500").SORT Key1:=Range("D1"), Order1:=xlDescending, Key2:= _
Range("E1"), Order2:=xlAscending, Key3:=Range("G1"),
Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal


I also can't get this (preferred) code to work:

With ActiveSheet

Dim Rng As Range
Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"),
Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending,
MatchCase:=False, Header:=xlYes

End With

Suzanne

 

[Joel]

columns U and B are backwards

With ActiveSheet

Dim Rng As Range
Set Rng = .Range(.Range("B1"), .Cells(Rows.Count, "U").End(xlUp))

Rng.SORT Key1:=.Cells(2, "D"), Order1:=xlDescending, Key2:=.Cells(2, "E"),
Order2:=xlAscending, Key3:=.Cells(2, "G"), Order3:=xlAscending,
MatchCase:=False, Header:=xlYes

End With

 

[Jim Thomlinson]

There must be something else going on here. I tested your prefered code and
it worked fine by me. The one thing that I notice is that your key work Sort
is all caps. Have you declared a procedure or sub or ??? somewhere called
SORT. If so then that could be the problem...

Sub test()
With ActiveSheet

Dim Rng As Range
Set Rng = .Range(.Range("U1"), .Cells(Rows.Count, "B").End(xlUp))

Rng.Sort Key1:=.Cells(2, "D"), Order1:=xlDescending, _
Key2:=.Cells(2, "E"), Order2:=xlAscending, _
Key3:=.Cells(2, "G"), Order3:=xlAscending, _
MatchCase:=False, Header:=xlYes
End With
End Sub

 

[Jim Thomlinson]

The order in which B and U appear makes no difference in terms of creating a
range object. In this case if B is always populated to the end of the data
set but U may not have data all the way to the end, the way that the OP has
created the code is correct. Yours may end up missing some rows.

 

[Suzanne]

This didn't fix it at all... the command wouldn't even run.

I thought that Range was the last column ("U") and the Rows.Count was the
starting point.