VBA Excel sort/ range Error with 2016

acdecal
acdecal used Ask the Experts™
on
Since migrating from Excel 2010 to 2016, the code below now produces an error:  Method sort of object range failed.   Used to work fine.  Can someone please help with this?


Private Sub SortAll()

      Range(Range("TopDescription"), Range("TopDescription").End(xlDown)).EntireRow.Sort Key1:=Range("TopCostcode"), Order1:=xlAscending, Key2:=Range("TopPhase" _
        ), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _
        , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
        xlSortNormal
       
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,
Please let us know the name range TopDescription & TopPhase
Try below:
Sub SortData()
Dim Ws As Worksheet
Set Ws = ActiveSheet

Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("TopDescription"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("TopDescription")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

Author

Commented:
That works!  But now I'm getting the same error with this code line;

Range(Range("a1"), Range("a" & Rows.Count).End(xlUp)).EntireRow.Sort Key1:=Range("J1")
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Please post your whole code to know the range, anyway try below:
Sub SortData()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row

Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("J1:J" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A1:J" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
OK Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial