Sorting by multiple columns

[dpaton05]

I have data that needs sorting by date first and then request number within each date range.

This is my 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?

 

[Michael M]

Is there data in ALL columns ?
AND
What does the request number look like ?

 

[dpaton05]

The request numbers looks like 10250 and there should be data in most columns.

 

[dpaton05]

By the way, I get a sort warning when I try and run this with both options creating an infinite loop.

 

[Michael M]

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

 

[dpaton05]

This code works for me Michael for sorting by date.

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]

Does this work for you...UNTESTED

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]

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]

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]

Actually, don't worry Michael, I don't need it anymore. But thanks anyway.