How to: Programmatically Sort Data in Worksheets Programmatically
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.
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
-
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);
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.
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
-
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.
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);
