VBA SortFields.Add2: What It Does and How to Use It

VBA & macros · Updated November 18, 2026

SortFields.Add2 is an Excel VBA method that adds a sort key to a sort operation. It works much like SortFields.Add, but its extra SubField argument lets you sort linked data types, such as Geography or Stocks, by a named detail such as population or price instead of only by the value displayed in the cell.

What SortFields.Add2 means

In Excel VBA, a sort is configured through a Sort object and its SortFields collection. Each sort field describes one rule: which cells to examine, whether to sort values or another supported kind of content, the direction, and any relevant options. Calling Add2 puts a rule into that collection; it does not, on its own, rearrange the worksheet. The sort is performed when the configured sort object’s Apply method runs.

The key difference from Add is that Add2 accepts the optional SubField argument. A cell containing a linked data type can represent more than the text shown in the grid. A Geography cell might have properties such as population or area, while a Stocks cell might expose properties such as price or market capitalization. SubField identifies which property should determine the order.

For ordinary text, numbers, and dates in normal cells, you usually do not need a subfield. If your code does not need to sort a linked data type by one of its properties, the familiar Add method may be sufficient. The name “Add2” is an object-model method name, not a separate worksheet function or a command to add two sort levels.

How Add2 works in a sort

A typical sort follows four steps: identify the full range to move, clear any existing sort rules, add one or more sort fields, and apply the sort. The key range normally excludes the header row, while the sort range includes the header and all columns that must stay together. If the range contains related information in adjacent columns, sorting only one column can separate values from their records.

The method’s commonly used arguments are Key, SortOn, Order, CustomOrder, DataOption, and SubField. Key is the range used to determine order; SortOn specifies what to sort on; Order sets ascending or descending order; and SubField names a linked-data-type property. The remaining arguments allow a custom sequence or specify how certain data should be interpreted. Most basic sorts need only a few of these settings.

With a single rule, Excel compares the chosen key values and moves entire rows within the range given to SetRange. With multiple rules, the first added field is the primary key, and later fields break ties. For example, a list can be sorted by department first and employee name second. Each key must correspond to the same records and row span as the overall sort range.

Example: sort linked data by a subfield

Suppose column A contains linked Geography cells, column B contains a region, and the header row is row 1. To sort the records by the linked Geography value’s Population subfield in ascending order, a macro can use Add2 like this. The worksheet is assumed to be named Data, with records in rows 2 through 20.

Sub SortByPopulation()
    Dim ws As Worksheet
    Dim sortRange As Range
    Dim keyRange As Range

    ' Save a copy of the workbook before running a macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sortRange = ws.Range("A1:B20")
    Set keyRange = ws.Range("A2:A20")

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=keyRange, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal, _
            SubField:="Population"
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Here, the overall range includes both columns so the region stays with its Geography record, while the key is only the Geography cells. SubField:="Population" tells Excel to use that linked property as the sort value. The subfield name must be available for the linked data type and should be supplied as the property name, not as a cell address. If the workbook uses different columns or a different number of rows, adjust both ranges to fit the actual data.

Before running the macro, save a copy of the workbook. VBA macros cannot be reversed with Ctrl+Z, and a sort changes the order of the records in the range.

Using Add2 for a regular column

Add2 can also be used without a subfield when the sort key is a conventional worksheet column. For instance, this example sorts a table by the values in column B from smallest to largest. It assumes headers in row 1 and data in rows 2 through 20.

Sub SortByColumnB()
    Dim ws As Worksheet
    Dim sortRange As Range
    Dim keyRange As Range

    ' Save a copy of the workbook before running a macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sortRange = ws.Range("A1:C20")
    Set keyRange = ws.Range("B2:B20")

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=keyRange, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

This use does not depend on a special property in column B. Leaving out SubField means the rule sorts on the key’s normal values. The same structure can be adapted to descending order by using xlDescending, or to another key by changing the key range. Keep the sort range wide enough to include every column belonging to the records.

When to choose Add2 or Add

Choose Add2 when you need to sort linked data-type cells by an exposed property, or when your Excel environment and project standards use the newer method consistently. Choose Add for a straightforward sort that does not need a linked-data subfield, particularly when compatibility with older Excel installations matters. The correct choice depends on both the data and the versions in which the macro must run.

  • Use Add2 with SubField: the sort key contains linked data types and the desired order depends on a specific property, not the cell’s displayed label.
  • Use Add2 without SubField: you want its normal sort behavior and know that the target Excel versions expose the method.
  • Use Add: the key is an ordinary range and your code needs to work in environments where Add2 may not be available.

Add2 is not automatically better for every sort, and it does not make an ordinary text value behave like a linked data type. It also does not create or refresh linked data. Its purpose is to define a sort field, including the option to specify a data-type subfield when that capability is relevant.

Sorting by more than one field

To sort by multiple criteria, clear the old rules and add each key before applying the sort. The first rule is primary. For example, to sort records by department in ascending order and then by employee name in ascending order, add the department key first and the name key second. Both keys should cover the same data rows; the full sort range should cover all record columns and the header.

When linked data types are involved, a subfield can be used for one of the keys if that is the intended criterion. Avoid adding several rules that contradict the order you want: later rules only determine order among rows tied on earlier keys. Also check that your key ranges do not include the header, or Excel may treat the header as a value rather than a label. Set .Header = xlYes when the sort range includes a header row.

Common problems and how to fix them

  • “Method or data member not found” at Add2: the Excel version or object library available to the project may not expose Add2. If the sort does not need a subfield, use Add where compatibility is required. A macro that specifically relies on linked-data subfield sorting needs an environment that supports that feature.
  • The sort runs, but the wrong values determine the order: check the Key range and, for linked data types, the spelling and availability of SubField. The key must refer to the intended records and property.
  • Values in different columns no longer match: the range passed to SetRange may be too narrow. Include the full records, not only the column used as the key.
  • The header moves into the data: include the header in the sort range and set .Header = xlYes. If there is no header, set the property to xlNo instead.
  • The macro reports that a worksheet cannot be found: confirm the sheet tab name matches the string used in Worksheets. A sheet’s tab name is not necessarily the same as its VBA code name.
  • The order seems unexpected for linked data: confirm that the cells really contain linked data types with the requested subfield. A text label that looks like a place name is not necessarily a Geography data type with a Population property.

Clearing .SortFields before adding rules is important when reusing a worksheet’s sort object. Otherwise, old criteria can remain and affect the result. If a sort runs on only part of the intended records, verify that the range has no gaps or missing rows and that the full dataset was included explicitly.

Version and platform considerations

This guidance is for Excel for Microsoft 365 on Windows. VBA macros cannot run in Excel for the web, so a workbook opened there cannot execute this procedure. On Mac, VBA is available in desktop Excel, but linked-data-type features and their availability can differ by Excel version and platform; confirm that the specific data type and subfield are supported in the environment where the macro will run.

Older versions may not recognize Add2, which can prevent code from compiling even if the macro never passes a SubField. If a workbook must be shared across different Excel releases, test the macro in each target version and prefer Add for ordinary sorts when broad compatibility is more important than subfield sorting. Do not assume that changing the method name alone preserves linked-data sorting: Add does not provide the SubField argument.

Frequently Asked Questions

What is the difference between SortFields.Add and Add2?
Add2 adds the optional SubField argument for sorting linked data types by a named property. For ordinary sort keys, both methods can define a sort field, subject to method availability in the Excel version being used.

Do I need Add2 to sort a normal Excel column?
No. A regular range of text, numbers, or dates does not require a data-type subfield. Use Add if it suits your compatibility needs, or use Add2 without the SubField argument in a version that supports it.

What should I put in the SubField argument?
Provide the name of an available property for the linked data type, such as Population for an appropriate Geography data type. The property must be supported by the data in those cells; a plain text cell does not acquire subfields just because a name is supplied.

Does Add2 sort the worksheet as soon as it is called?
No. It adds a criterion to the sort-fields collection. Set the sort range and other options, then call .Apply to execute the sort.

Can I undo a VBA sort with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a copy before running code that changes the order of workbook data.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.