Sort multiple columns

[tiredofit]

I want to sort three columns of data.

Dim rng As Range
   
    Set rng = Sheet1.Range("C10:E100")
   
    With Sheet1.Sort.SortFields
   
        .Clear
   
        .Add2 Key:=rng, _
              SortOn:=xlSortOnValues, _
              Order:=xlAscending, _
              DataOption:=xlSortNormal
             
    End With

    With Sheet1.Sort
   
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
       
    End With
   
    Set rng = Nothing

The code fails on this line:

.Apply

with the message:

The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

Can someone tell me what's wrong?

Recording a macro gives this:

 Range("C10:E100").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "C10:E100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C10:E100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

which works.



Thanks

 

[Fluff]

You have to specify a single column/cell for each sort field.

 

[tiredofit]

You have to specify a single column/cell for each sort field.

But why did the recorded version work?

 

[Fluff]

You added that bit after I had replied, so didn't see it.

 

[tiredofit]

You added that bit after I had replied, so didn't see it.

Yes, I did.

 

[Fluff]

Just tested your recorder macro & it doesn't work for me.

 

[tiredofit]

Just tested your recorder macro & it doesn't work for me.

I re-recorded another and came up with this:

Range("C10:E100").Select
 
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "C10:E100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C10:E100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Can you record a macro and see what code it generates?

 

[Fluff]

That looks to be the same code as you posted earlier.
Also I don't see how the macro recorder could have given you that code, as you need to specify the columns to sort.

 

[tiredofit]

I re-recorded another and came up with this:

Range("C10:E100").Select
 
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "C10:E100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C10:E100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Can you record a macro and see what code it generates?

That looks to be the same code as you posted earlier.
Also I don't see how the macro recorder could have given you that code, as you need to specify the columns to sort.

It's my mistake when I copied the code on here.

The problem lies with the range, there are three instances of it and only two are the same.

The middle one should read C11:E100, as opposed to C10:E100.

Thanks for helping.

 

[Fluff]

Are you saying it's sorted?