Filter with a main criterion and sub criterion

Question

Consider a table with three columns A, B and C. I want to sort this table using the following two criteria.

1. Main Criterion: Sort the whole table according to column C so that entries in C is increasing. For example, 2009, 2009, 2010, 2010, etc.
2. Sub Criterion: When the entries in column C are the same, say, 2009, I want to sort the table so that the entries in column B are decreasing. For example, if there are two 2009 in C, then I want B to have 100 and 99.

How do I achieve this in Excel, please?

Answer 1

This code is adapted from Recorded code. Starting with data like:

Running this macro:

Sub Macro1()
With ActiveWorkbook.Worksheets("Sheet1").Sort
   .SortFields.Clear
   .SortFields.Add Key:=Range("C1:C20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .SortFields.Add Key:=Range("B1:B20"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
   .SetRange Range("A1:C20")
   .Header = xlNo
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
End With
End Sub

Will produce: