Sort Errors

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

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: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
 
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
 
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
 
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.
 
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.
 
Back
Top