How to: Sort Data in Worksheets Programmatically

  • Article

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

  • Document-level projects

  • Application-level projects

Microsoft Office version

  • Excel 2003

  • Excel 2007

For more information, see Features Available by Application and Project Type.

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.

Starting in Visual Studio 2008 Service Pack 1 (SP1), you can sort data at run time in application-level add-ins.

Sorting Data in a Document-Level Customization

To sort data in a NamedRange control

  • 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.

    Me.Fruits.Sort( _
    Key1:=Me.Fruits.Columns(1), Order1:=Excel.XlSortOrder.xlAscending, _
    Key2:=Me.Fruits.Columns(2), Order2:=Excel.XlSortOrder.xlAscending, _
    Orientation:=Excel.XlSortOrientation.xlSortColumns, _
    Header:=Excel.XlYesNoGuess.xlNo, _
    SortMethod:=Excel.XlSortMethod.xlPinYin, _
    DataOption1:=Excel.XlSortDataOption.xlSortNormal, _
    DataOption2:=Excel.XlSortDataOption.xlSortNormal, _
    DataOption3:=Excel.XlSortDataOption.xlSortNormal)

    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 Microsoft.Office.Tools.Excel.ListObject control named fruitList in a worksheet named Sheet1.

To sort data in a ListObject control

Sorting Data in an Application-Level Add-In

To sort data in a native range

  • Call the Sort(Object, XlSortOrder, Object, Object, XlSortOrder, Object, XlSortOrder, XlYesNoGuess, Object, Object, XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortDataOption, XlSortDataOption) method of the native Excel Range control. The following example requires a native Excel control named Fruits on a worksheet.

    Dim Fruits As Excel.Range = Me.Application.Range("A1", "B2")
Fruits.Sort( _
    Key1:=Fruits.Columns(1), Order1:=Excel.XlSortOrder.xlAscending, _
    Key2:=Fruits.Columns(2), Order2:=Excel.XlSortOrder.xlAscending, _
    Orientation:=Excel.XlSortOrientation.xlSortColumns, _
    Header:=Excel.XlYesNoGuess.xlNo, _
    SortMethod:=Excel.XlSortMethod.xlPinYin, _
    DataOption1:=Excel.XlSortDataOption.xlSortNormal, _
    DataOption2:=Excel.XlSortDataOption.xlSortNormal, _
    DataOption3:=Excel.XlSortDataOption.xlSortNormal)

    Excel.Range Fruits = Application.get_Range("A1", "B3");
    Fruits.Sort(
    Fruits.Columns[1, missing], Excel.XlSortOrder.xlAscending,
    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);

To sort data in a ListObject control

  • Call the Sort(Object, XlSortOrder, Object, Object, XlSortOrder, Object, XlSortOrder, XlYesNoGuess, Object, Object, XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortDataOption, XlSortDataOption) 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.

    Dim fruitList As Excel.ListObject = CType(Application.ActiveSheet,  _
    Excel.Worksheet).ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, _
    Application.Range("A1", "B2"))
fruitList.Range.Sort( _
Key1:=fruitList.ListColumns(1).Range, Order1:=Excel.XlSortOrder.xlAscending, _
Key2:=fruitList.ListColumns(2).Range, Order2:=Excel.XlSortOrder.xlAscending, _
Orientation:=Excel.XlSortOrientation.xlSortColumns, _
Header:=Excel.XlYesNoGuess.xlYes)

    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, missing); 
    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);

See Also

Tasks

How to: Automatically Fill Ranges with Incrementally Changing Data

How to: Refer to Worksheet Ranges in Code

How to: Apply Styles to Ranges in Workbooks

Concepts

Working with Worksheets

NamedRange Control

ListObject Control

The Variable missing and Optional Parameters in Office Solutions

Change History

Date

History

Reason

July 2008

Added a code example that can be used in an application-level add-in.

Customer feedback.