Run-time error 438 in Excel while using Sort function

Question

I'm trying to write a code that will remove duplicates from my list.

It worked well on my mac, but when I tried to run it on windows I got the runtime error 438:

object doesn't support this property or method.

What went wrong?

Range("E2:E150").Select
 ActiveWorkbook.Worksheets("NOS").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("NOS").Sort.SortFields.Add2 Key:=Range("E2"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("NOS").Sort
    .SetRange Range("E2:G150")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
 End With
Range("E1").Select

End Sub

Which version of Excel are you using? The [`.SortFields.Add2` method](https://learn.microsoft.com/en-us/office/vba/api/excel.sortfields.add2) was introduced in 2016 if your Excel is older you must use the old [`.SortFields.Add` method](https://learn.microsoft.com/en-us/office/vba/api/excel.sortfields.add) (which lacks the ability of using the `SubField` parameter). • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Feb 05 '19 at 09:16
Word of advice: lose the .Select lines. They're useless in your code (and rarely are useful at all). Additionally you are better off referring to the Workbook using its name or index than when you are referring to the active workbook which poses a wide array of problems. Good luck — Tim Stack, Feb 05 '19 at 09:26
@Pᴇʜ thank you very much! it definitely solved my problem — Dor, Feb 05 '19 at 09:27

score 1 · Accepted Answer · answered Feb 05 '19 at 13:26

The issue is that you probably use an Excel version released before 2016. The .SortFields.Add2 method was introduced in 2016 if your Excel is older you must use the old .SortFields.Add method (which lacks the ability of using the SubField parameter).

You might benefit from reading How to avoid using Select in Excel VBA, your .Select statements are pretty useless and not needed.

With ActiveWorkbook.Worksheets("NOS").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("E2"), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("E2:G150")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
End With

Run-time error 438 in Excel while using Sort function

1 Answers1