Runtime error 1004 Application-defined or object defined error" on data sort
 

Hi,

I receive the error message mentioned above using the following macro:

Application.Goto Reference:="Data"
Selection.Sort Key1:=Range("P12"), Order1:=xlDescending, Key2:=Range("B12"), Order2:=xlAscending, Key3:=Range("M12"), Order3:=xlAscending, Key4:=Range("K12"), Order4:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I do not receive any error message with the following macro (which has one less sort key):

Application.Goto Reference:="Data"
Selection.Sort Key1:=Range("P12"), Order1:=xlDescending, Key2:=Range("B12"), Order2:=xlAscending, Key3:=Range("M12"), Order3:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I can interchange Key3 in the above macro from Range("M12") to Range("K12") without receiving an error message, so it can't be down to the format of the data in column K.

Any help would be appreciated.

Thanks

Chris

Runtime error 1004 Application-defined or object defined error"on data sort
 

Chris,

The Range.Sort method is limited to three key fields, hence the error. If you have Excel 2007 or later, try the code called "SortXL2007" below. If you are using 2003, try the other code.

Regards,

Ben

Sub SortXL2007()
Dim lFirstRow As Long
Dim lLastRow As Long
Dim vCells() As String

vCells() = Split(Range("Data").Address, ":")
lFirstRow = Range(vCells(0)).Row
lLastRow = Range(vCells(1)).Row

With ActiveWorkbook.Worksheets("Sheet1").sort
.SortFields.Clear
.SortFields.Add _
Key:=Range("$P$" & lFirstRow & ":$P$" & lLastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
.SortFields.Add _
Key:=Range("$B$" & lFirstRow & ":$B$" & lLastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
.SortFields.Add _
Key:=Range("$M$" & lFirstRow & ":$M$" & lLastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
.SortFields.Add _
Key:=Range("$K$" & lFirstRow & ":$K$" & lLastRow), _
SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.SetRange Range("Data")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

Sub SortXL2003()

With Range("Data")
.sort Key1:=Range("K12"), Order1:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.sort Key1:=Range("P12"), Order1:=xlDescending, _
Key2:=Range("B12"), Order2:=xlAscending, Key3:=Range("M12"), _
Order3:=xlAscending, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

End Sub