Programmatically Sort Data in Excel After Using Subtotal Function Using C#

Question

I have data that I want to apply the subtotal function to and then sort the resulting data in descending order. So far I've managed to get my code to apply the subtotal function and then change the view so it only displays the column I want sorted. I cannot figure out how to use the .Sort method at this point to only sort the visible data.

My code to implement the subtotals:

xcel.Range range = excelWorksheet.UsedRange;
        range.Subtotal(1, Excel.XlConsolidationFunction.xlSum, Type.Missing, (object)true, (object)false, Excel.XlSummaryRow.xlSummaryBelow);

My code to change the view to just the column I want sorted:

excelWorksheet.Outline.ShowLevels(2, Type.Missing);

At this point I've tried many variations of the .Sort method using just about all the ranges I can think of (visible range, explicitly stating the column I want, the entire worksheet, the used range before the subtotal filter, the used range after the subtotal filter). I've also tried recording a macro inside Excel, but I cannot figure out how to convert that back to C#.

The Excel macro that does exactly that I need is:

Range("G3").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G3"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A2:G2853")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Does anyone know how to sort the data after subtotal grouping has been applied?

Answer 1

I had the same issue but I just took the Macro (very similar to yours) and I tried to match it against the Excel method Sort. The below worked for me:

     range.Sort(Key1: range.Columns[8], Order1: ExcelM.XlSortOrder.xlDescending,
                Header: ExcelM.XlYesNoGuess.xlYes,MatchCase: false,
                Orientation: ExcelM.XlSortOrientation.xlSortColumns,
                SortMethod:ExcelM.XlSortMethod.xlPinYin
                );