Export (0) Print
Expand All

How to: Programmatically Sort Data in Worksheets

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 application-level projects for Excel 2013 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

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)
    

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

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

    Me.fruitList.Range.Sort( _
        Key1:=Me.fruitList.ListColumns(1).Range, Order1:=Excel.XlSortOrder.xlAscending, _
        Key2:=Me.fruitList.ListColumns(2).Range, Order2:=Excel.XlSortOrder.xlAscending, _
        Orientation:=Excel.XlSortOrientation.xlSortColumns, _
        Header:=Excel.XlYesNoGuess.xlYes)
    

To sort data in a native range

  • Call the Sort method of the native Excel Microsoft.Office.Interop.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)
    

To sort data in a ListObject control

  • Call the Sort method of the Range property of the native Excel Microsoft.Office.Interop.Excel.ListObject control. The following example assumes that you have a native Excel Microsoft.Office.Interop.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)
    
Show:
© 2014 Microsoft