Excel VBA Run-Time Error 1004

Accepted answer +4 upvotes

The sort by box is blank, that is your problem. I never have used a Sort object like your doing, but I can see that you have not defined a key, or a range to sort by, just the range to be sorted. A key should be defined such as Range(“A4”) or something. I looked it up, it should have .sortfields.add (range) in it, such as:

'Sort the active rows

    With ActiveWorkbook.Worksheets("Product Backlog").Sort
        .SetRange Range("A4:F51")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin

        .SortFields.Add Key:=Range("A4:F51").Columns(1), SortOn:=xlSortOnValues, _
         Order:=xlDescending, DataOption:=xlSortNormal

        .Apply
    End With

I use the Sort function as follows:

ActiveWorkbook.Worksheets("Product Backlog").Range("A4:F51").Sort _
    Key1:= ActiveWorkbook.Worksheets("Product Backlog").Range("A4:F51").Columns(1), _
    Header:= xlYes, _
    Orientation:=xlSortColumns, _
    MatchCase:=False, _
    SortMethod:=xlPinYin