NamedRange.Sort Method

Reference

Definition

Namespace:: Microsoft.Office.Tools.Excel

Assembly:: Microsoft.Office.Tools.Excel.dll

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

Sorts the data in a NamedRange control.

public object Sort (object Key1, Microsoft.Office.Interop.Excel.XlSortOrder Order1 = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, object Key2, object Type, Microsoft.Office.Interop.Excel.XlSortOrder Order2 = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, object Key3, Microsoft.Office.Interop.Excel.XlSortOrder Order3 = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Microsoft.Office.Interop.Excel.XlYesNoGuess Header = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, object OrderCustom, object MatchCase, Microsoft.Office.Interop.Excel.XlSortOrientation Orientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortRows, Microsoft.Office.Interop.Excel.XlSortMethod SortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin, Microsoft.Office.Interop.Excel.XlSortDataOption DataOption1 = Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, Microsoft.Office.Interop.Excel.XlSortDataOption DataOption2 = Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, Microsoft.Office.Interop.Excel.XlSortDataOption DataOption3 = Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal);

abstract member Sort : obj * Microsoft.Office.Interop.Excel.XlSortOrder * obj * obj * Microsoft.Office.Interop.Excel.XlSortOrder * obj * Microsoft.Office.Interop.Excel.XlSortOrder * Microsoft.Office.Interop.Excel.XlYesNoGuess * obj * obj * Microsoft.Office.Interop.Excel.XlSortOrientation * Microsoft.Office.Interop.Excel.XlSortMethod * Microsoft.Office.Interop.Excel.XlSortDataOption * Microsoft.Office.Interop.Excel.XlSortDataOption * Microsoft.Office.Interop.Excel.XlSortDataOption -> obj

Public Function Sort (Optional Key1 As Object, Optional Order1 As XlSortOrder = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Optional Key2 As Object, Optional Type As Object, Optional Order2 As XlSortOrder = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Optional Key3 As Object, Optional Order3 As XlSortOrder = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Optional Header As XlYesNoGuess = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, Optional OrderCustom As Object, Optional MatchCase As Object, Optional Orientation As XlSortOrientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortRows, Optional SortMethod As XlSortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin, Optional DataOption1 As XlSortDataOption = Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, Optional DataOption2 As XlSortDataOption = Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, Optional DataOption3 As XlSortDataOption = Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal) As Object

Parameters

Key1: Object

The first sort field, as either text (a range name) or a Range object ("Dept" or Cells(1, 1), for example).

Order1: XlSortOrder

The sort order for the field or range specified in Key1.Can be one of the following XlSortOrder values: xlDescending. Sorts Key1 in descending order. xlAscending. Sorts Key1 in ascending order.

Key2: Object

The second sort field, as either text (a range name) or a Range object. If you omit this argument, there is no second sort field.

Type: Object

Specifies which elements are to be sorted. Use this argument only when sorting PivotTable reports.Can be one of the following XlSortType values: xlSortLabels. Sorts the PivotTable report by labels. xlSortValues. Sorts the PivotTable report by values.

Order2: XlSortOrder

The sort order for the range specified in Key2. Cannot be used when sorting PivotTable reports.Can be one of the following XlSortOrder values: xlDescending. Sorts Key2 in descending order. xlAscending. Sorts Key2 in ascending order.

Key3: Object

The third sort field, as either text (a range name) or a Range object. If you omit this argument, there is no third sort field. Cannot be used when sorting PivotTable reports.

Order3: XlSortOrder

The sort order for the field or range specified in Key3. Cannot be used when sorting PivotTable reports.Can be one of the following XlSortOrder values: xlDescending. Sorts Key3 in descending order. xlAscending. Sorts Key3 in ascending order.

Header: XlYesNoGuess

Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.Can be one of the following XlYesNoGuess values: xlGuess. Let Microsoft Office Excel determine whether there is a header, and to determine where it is, if there is one. xlNo. (The entire range should be sorted). xlYes. (The entire range should not be sorted).

OrderCustom: Object

This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom, a normal sort is used.

MatchCase: Object

true to do a case-sensitive sort; false to do a sort that is not case sensitive. Cannot be used when sorting PivotTable reports.

Orientation: XlSortOrientation

The sort orientation.Can be one of the following XlSortOrientation values: xlSortRows. Sorts by row. xlSortColumns. Sorts by column.

SortMethod: XlSortMethod

The type of sort. Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that you have selected or installed.Can be one of the following XlSortMethod values: xlStroke. Sorting by the quantity of strokes in each character. xlPinYin. Phonetic Chinese sort order for characters.

DataOption1: XlSortDataOption

Specifies how to sort text in key1. Cannot be used when sorting PivotTable reports.Can be one of the following XlSortDataOption values: xlSortTextAsNumbers. Treat text as numeric data for the sort. xlSortNormal. Sorts numeric and text data separately.

DataOption2: XlSortDataOption

Specifies how to sort text in key2. Cannot be used when sorting PivotTable reports.Can be one of the following XlSortDataOption values: xlSortTextAsNumbers. Treats text as numeric data for the sort. xlSortNormal. Sorts numeric and text data separately.

DataOption3: XlSortDataOption

Specifies how to sort text in key3. Cannot be used when sorting PivotTable reports.Can be one of the following XlSortDataOption values: xlSortTextAsNumbers. Treats text as numeric data for the sort. xlSortNormal. Sorts numeric and text data separately.

Returns

Object

Examples

The following code example places a set of integers in cells A1 through A5 and then uses the Sort method to sort the data in ascending order.

This example is for a document-level customization.

private void SortNamedRange()
{
    // Set some data in a range of cells.
    this.Range["A1"].Value2 = 30;
    this.Range["A2"].Value2 = 10;
    this.Range["A3"].Value2 = 20;
    this.Range["A4"].Value2 = 50;
    this.Range["A5"].Value2 = 40;

    // Create a NamedRange that refers to the range.
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(this.Range["A1", "A5"],
        "namedRange1");

    // Sort the NamedRange.
    namedRange1.Sort(this.Range["A1", "A5"],
        Excel.XlSortOrder.xlAscending,
        missing,
        missing,
        Excel.XlSortOrder.xlAscending,
        missing,
        Excel.XlSortOrder.xlAscending,
        Excel.XlYesNoGuess.xlNo,
        missing,
        missing,
        Excel.XlSortOrientation.xlSortColumns,
        Excel.XlSortMethod.xlStroke,
        Excel.XlSortDataOption.xlSortNormal,
        Excel.XlSortDataOption.xlSortNormal,
        Excel.XlSortDataOption.xlSortNormal);
}

Private Sub SortNamedRange()
    ' Set some data in a range of cells.
    Me.Range("A1").Value2 = 30
    Me.Range("A2").Value2 = 10
    Me.Range("A3").Value2 = 20
    Me.Range("A4").Value2 = 50
    Me.Range("A5").Value2 = 40

    ' Create a NamedRange that refers to the range.
    Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
        = Me.Controls.AddNamedRange(Me.Range("A1", "A5"), _
        "namedRange1")

    ' Sort the NamedRange.
    namedRange1.Sort(Me.Range("A1", "A5"), _
        Excel.XlSortOrder.xlAscending, , , _
        Excel.XlSortOrder.xlAscending, , _
        Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlNo, , , _
        Excel.XlSortOrientation.xlSortColumns, _
        Excel.XlSortMethod.xlStroke, _
        Excel.XlSortDataOption.xlSortNormal, _
        Excel.XlSortDataOption.xlSortNormal, _
        Excel.XlSortDataOption.xlSortNormal)
End Sub

Remarks

The settings for Header, Order1, Order2, Order3, OrderCustom, and Orientation are saved, for the particular worksheet, each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Set these arguments explicitly each time you use this method, if you choose not to use the saved values.

Text strings that are not convertible to numeric data are sorted normally.

If no arguments are defined with this method, Microsoft Office Excel will sort the selection in ascending order.

Optional Parameters

For information on optional parameters, see Optional Parameters in Office Solutions.

Applies to

Share via