Sorting Multiple Keys in VBA; Runtime Error 450: Wrong number of arguments or invalid property assignment

Question

I have been writing this VBA code to help me sort a database of information I have (Name, Invoice#, Address, etc). I recently learned how to sort in VBA without selecting the actual range in the worksheet and with that I tried sorting with multiple keys (key1, key2, key3), but no luck. I keep getting a Run time error 450: Wrong number of arguments or invalid property assignment when running this code. I want to be able to sort a range of data multiple times, so for instance sort a range (A:K) by column F, then by E, then by B etc.

Here is the code I have been using for sorting my columns:

ActiveSheet.Range("A:K").Sort _
Key1:=ActiveSheet.Range("F2"), Order1:=xlAscending, HEADER:=xlYes, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
Key2:=ActiveSheet.Range("E2"), Order2:=xlAscending, HEADER:=xlYes, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption2:=xlSortNormal, _
Key3:=ActiveSheet.Range("D2"), Order3:=xlAscending, HEADER:=xlYes, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption3:=xlSortNormal

It is weird, because at first I tried running the first 2 keys (key1, key2) and it worked perfectly fine, then I copied and pasted down to create the next key (key3) and the error came up. When I try to debug, all the code above gets highlighted.

The only other way I can think of is to run the code where the range (A:K) gets selected in the worksheet, but I don't want to do that.

You're specifying parameters for HEADER, OrderCustom, and Orientation multiple times. I'd try removing the duplicate parameters first. See the param list here. — Comintern
I'd start by recording a macro that does the sort you want. Copy and paste the code here to compare or maybe it'll be enough info so you can fix it yourself — dbmitch

Unknown Unknown · Accepted Answer · 2016-09-16T04:36:19

It looks like you are trying to rework some recorded macro code. The recorded code for a recorded Range.Find method is very verbose. Here is all that you should require.

With Sheets("Sheet1")
    With Intersect(.Range("A1").CurrentRegion, .Range("A:K"))
        .Cells.Sort Key1:=.Columns(6), Order1:=xlAscending, _
                    Key2:=.Columns(5), Order2:=xlAscending, _
                    Key3:=.Columns(4), Order3:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
    End With
End With

TBH, I do not know if you can simply add Ordercustom:=1 to each line of that. IIRC, custom sorts cannot be performed on more than a single field at a time. In that case, perform three sorts, each with a Ordercustom:=1 but remember to do it in reverse order; e.g. column D first, then E and finally the primary sort on column F.

Please note that there are a maximum of three columns to set the primary sort and secondary sorts to with this method. If you require more, sort on those first and run a subsequent sort on the last three prevalent columns.

Sorting Multiple Keys in VBA; Runtime Error 450: Wrong number of arguments or invalid property assignment

1 Answers