Edit

Programmatically sort data in worksheets

  • Article

You can sort data that is contained in worksheet ranges and lists at run time. The following code sorts a multi-column range named Fruits by the data in the first column, and then by the data in the second column.

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

Sort data in a document-level customization

To sort data in a NamedRange control

  1. Call the Sort method of the NamedRange control. The following example requires a NamedRange control named Fruits on a worksheet. This code must be placed in a sheet class, not in the ThisWorkbook class.

    this.Fruits.Sort(
    this.Fruits.Columns[1, missing], Excel.XlSortOrder.xlAscending,
    this.Fruits.Columns[2, missing], missing, Excel.XlSortOrder.xlAscending,
    missing, Excel.XlSortOrder.xlAscending,
    Excel.XlYesNoGuess.xlNo, missing, missing, 
    Excel.XlSortOrientation.xlSortColumns,
    Excel.XlSortMethod.xlPinYin,
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal);

    Place the following code in Sheet1.vb or Sheet1.cs to sort data in a ListObject control. The code assumes that you have a ListObject control named fruitList in a worksheet named Sheet1.

To sort data in a ListObject control

  1. Call the Sort method of the Range property of the ListObject host control.

    this.fruitList.Range.Sort(
    this.fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending,
    this.fruitList.ListColumns[2].Range, missing, Excel.XlSortOrder.xlAscending,
    missing, Excel.XlSortOrder.xlAscending, 
    Excel.XlYesNoGuess.xlYes, missing, missing, 
    Excel.XlSortOrientation.xlSortColumns,
    Excel.XlSortMethod.xlPinYin, 
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal);

Sort data in a VSTO Add-in

To sort data in a native range

  1. Call the Sort method of the native Excel Range control. The following example requires a native Excel control named Fruits on a worksheet.

    Excel.Range Fruits = Application.get_Range("A1", "B3");
    Fruits.Sort(
    Fruits.Columns[1], Excel.XlSortOrder.xlAscending,
    Fruits.Columns[2], missing, Excel.XlSortOrder.xlAscending,
    missing, Excel.XlSortOrder.xlAscending,
    Excel.XlYesNoGuess.xlNo, missing, missing,
    Excel.XlSortOrientation.xlSortColumns,
    Excel.XlSortMethod.xlPinYin,
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal);

To sort data in a ListObject control

  1. Call the Sort method of the Range property of the native Excel ListObject control. The following example assumes that you have a native Excel ListObject control named fruitList in the active worksheet.

    Excel.ListObject fruitList = 
     ((Excel.Worksheet)Application.ActiveSheet).
         ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange,
         Application.get_Range("A1", "B3"), 
         missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo); 
    fruitList.Range.Sort(
        fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending,
        fruitList.ListColumns[2].Range, missing, Excel.XlSortOrder.xlAscending,
        missing, Excel.XlSortOrder.xlAscending,
        Excel.XlYesNoGuess.xlYes, missing, missing,
        Excel.XlSortOrientation.xlSortColumns,
        Excel.XlSortMethod.xlPinYin,
        Excel.XlSortDataOption.xlSortNormal,
        Excel.XlSortDataOption.xlSortNormal,
        Excel.XlSortDataOption.xlSortNormal);

Related content