Sort multiple columns

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,583
Office Version
  1. 2019
Platform
  1. Windows
I want to sort three columns of data.

Code:
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:

Code:
.Apply

with the message:

Code:
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:

Code:
 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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
You have to specify a single column/cell for each sort field.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
You added that bit after I had replied, so didn't see it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Just tested your recorder macro & it doesn't work for me.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,583
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Just tested your recorder macro & it doesn't work for me.
I re-recorded another and came up with this:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,583
Office Version
  1. 2019
Platform
  1. Windows
I re-recorded another and came up with this:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Are you saying it's sorted?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,700
Messages
5,766,010
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top