How to: 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 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

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

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

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

Sorting Data in an Application-Level Add-In

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)
  

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

  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

Optional Parameters in Office Solutions