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
– BruceWayne, Commented Dec 1, 2016 at 18:30
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
– Mathieu Guindon, Commented Dec 1, 2016 at 18:52

user3598756 · Accepted Answer · 2016-12-01 18:46:28Z

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 1, 2016 at 18:46

answered Dec 1, 2016 at 18:40

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BruceWayne Over a year ago

Good call using With

anonymous Over a year ago

@user3598756 Thank you! I completely forgot I removed a column yesterday so my data ends at Y now.

Wedge · Accepted Answer · 2016-12-01 19:21:42Z

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 1, 2016 at 19:21

Wedge

1,8561 gold badge11 silver badges15 bronze badges

1 Comment

anonymous Over a year ago

Awesome! Thanks for the advice. I am continually adjusting this data range to get my program to work so this will work much better.

Collectives™ on Stack Overflow

VBA Sort method of Range class failed

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related