SortFields.Add2 method (Excel)
Creates a new sort field and returns a SortFields object that can optionally sort data types with the SubField defined.
Syntax
expression.Add2 (Key, SortOn, Order, CustomOrder, DataOption, SubField)
expression A variable that represents a SortFields object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
Key | Required | Range | Specifies a key value for the sort. |
SortOn | Optional | Variant | The field to sort on. |
Order | Optional | Variant | Specifies the sort order. |
CustomOrder | Optional | Variant | Specifies if a custom sort order should be used. |
DataOption | Optional | Variant | Specifies the data option. |
SubField | Optional | Variant | Specifies the field to sort on for a data type (such as Population for Geography or Volume for Stocks). |
Return value
SortField
Remarks
This API includes support for sorting off a SubField from data types, such as Geography or Stocks. You can also use the Add method if sorting by a data type is not needed.
Unlike in formulas, SubFields don't require brackets to include spaces.
Example
This example sorts a table, Table1 on Sheet1, by Column1 in ascending order based off the SubField Population on Geography data types.
The Clear method is called before to ensure that the previous sort is cleared so that a new one can be applied.
The Sort object is called to apply the added sort to Table1.
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add2 _
Key:=Range("Table1[[#All],[Column1]]"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal, _
SubField:="Population"
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for