VBA Sort method of Range class failed

Question

I know there are several threads on this topic but none of the answers have helped resolved this error.

I am trying to sort a table using three keys but receive the error, "Run-time error '1004': Sort method of Range class failed"

I've tried changing "Range("L2")" to ".Range("L2")" and received the error, "Compile error: Invalid or unqualified reference"

I've tried specifying the actual range instead of using columns, I've removed the last two keys and tried just the first, still received the run-time error.

shtData.Activate

shtData.Range(Range("A2"), Range("Z8000").End(xlUp)).Sort _
Key1:=Range("L2"), Order1:=xlAscending, _
Key2:=Range("M2"), Order2:=xlAscending, _
Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlYes

If you have any suggestions, I'd greatly appreciate it. I had this working yesterday and my excel crashed and did not recover the changes I made, I cannot figure out why I can't get it to work today.

You need to fully qualify the ranges, which can provide errors if you don't. I.e. `shtData.Range(shtData.Range("A2"), shtData.Range("Z8000").End(xlUp)).Sort`, and the same with your `Keys`. — BruceWayne, Dec 01 '16 at 18:30
@user3598756 understood, but it is best practice to [avoid using `.Activate`/`.Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Dec 01 '16 at 18:46
The dot is a member access. There's the global-scope `Range` which implicitly refers to the active sheet, and then there's a worksheet's `.Range` property, which refers to a range *in that worksheet*. A `With` block gives you an object to qualify members with - of course using an unqualified `.Range` is a compile error. — Mathieu Guindon, Dec 01 '16 at 18:52
@BruceWayne, sure! Just wanted to make it clear that couldn't raise the error experienced by OP. Unless Mat's comment says the opposite (I must read it carefully...) — user3598756, Dec 01 '16 at 19:06

user3598756 · Accepted Answer · 2016-12-01T18:46:28.657

3

you most probably have no data in "Z" column

if data rows range can be safely sized by column "A" not empty cells then go like follows

Option Explicit

Sub main()
    Dim shtData As Worksheet

    Set shtData = Worksheets("Data") '<--| some setting of 'shtData'
    With shtData
        .Range("Z2", .Cells(.Rows.Count, "A").End(xlUp)).Sort _
        Key1:=.Range("L2"), Order1:=xlAscending, _
        Key2:=.Range("M2"), Order2:=xlAscending, _
        Key3:=.Range("B2"), Order3:=xlAscending, _
        Header:=xlYes
    End With
End Sub

edited Dec 01 '16 at 18:46

answered Dec 01 '16 at 18:40

user3598756

28,893
4
18
28

Good call using `With` – BruceWayne Dec 01 '16 at 18:47
@user3598756 Thank you! I completely forgot I removed a column yesterday so my data ends at Y now. – anonymous Dec 01 '16 at 18:52

score 1 · Answer 2 · answered Dec 01 '16 at 19:21

1

If it is possible you could end up changing amount of columns again in the future, you could do something like.

With shtData
Range(Range("A2"), Cells(Range("A8000").End(xlUp).Row, Range("ZZ2").End(xlLeft).Column)

That way it will automatically size the sort area for however many columns you are using as well as rows.

answered Dec 01 '16 at 19:21

Wedge

1,766
1
8
14

Awesome! Thanks for the advice. I am continually adjusting this data range to get my program to work so this will work much better. – anonymous Dec 01 '16 at 19:23

VBA Sort method of Range class failed

2 Answers2