Sorting by multiple columns

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have data that needs sorting by date first and then request number within each date range.

This is my code
VBA Code:
Sub Sorting()
'This code sorts by the date first and within each date, it sorts the rows by the request number
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A3"), order:=xlAscending
     .SortFields.Add Key:=Range("C3"), order:=xlAscending
     .SetRange Range("A3", Range("O" & Rows.Count).End(xlDown))
     .Header = xlYes
     .Apply
End With
End Sub

  • The header row is row 3
  • Date is column A
  • Request number is column C
What is wrong with my code as it sorts by date but will not sort within that by request number?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Is there data in ALL columns ?
AND
What does the request number look like ?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The request numbers looks like 10250 and there should be data in most columns.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
By the way, I get a sort warning when I try and run this with both options creating an infinite loop.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

You'll get an error if you try to sort with a blank column somewhere in the range !!
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This code works for me Michael for sorting by date.
VBA Code:
Sub SortCells()

Range("A4", Range("O" & Rows.Count).End(xlDown).Address).Sort Key1:=Range("A4"), Order1:=xlAscending
End Sub

How do I change it to sort by the request number within each date?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Does this work for you...UNTESTED

VBA Code:
Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveSheet
.Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("A3:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("C3:C" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A3:O" & lr)
        .Header = xlYes
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
There may be things entered under the data so I can't use the xlup method and rows.count to find the number of rows. There should not be any gaps in the date so I was thinking the xldown method on column A. I am just a little unsure on the syntax to change around the third line of code.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Michael,

That sorts everything by date (column A) but it only sorts the first instance of the date by request number. By that I mean that for the first date going down the list, the request number is sorted but for every additional group of dates in the list, the request numbers are not sorted.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Actually, don't worry Michael, I don't need it anymore. But thanks anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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