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 VSTO add-in projects for Excel. For more information, see Features Available by Office Application and Project Type.

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.

            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

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

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

  1. 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)
    

Working with Worksheets
How to: Programmatically Automatically Fill Ranges with Incrementally Changing Data
How to: Programmatically Refer to Worksheet Ranges in Code
How to: Programmatically Apply Styles to Ranges in Workbooks
NamedRange Control
ListObject Control
Optional Parameters in Office Solutions

Show: