VBA Sort Syntax: Examples for Excel Ranges

VBA & macros · Updated November 18, 2026

In Excel VBA, the basic sort syntax is Range.Sort: specify the range to rearrange, a key column, its sort order, and whether the range has a header. For multiple sort levels or more control, use the worksheet’s Sort object with SortFields.

The basic VBA sort syntax

Range.Sort is the shortest way to sort a rectangular block of cells. In a call, the range before .Sort is the entire block that Excel may rearrange; Key1 identifies the first sort field. Order1 sets that field to ascending or descending, and Header tells Excel whether the first row contains column labels.

A simplified pattern is someRange.Sort Key1:=keyCell, Order1:=xlAscending, Header:=xlYes. Named arguments make the call easier to read and avoid relying on the order of optional arguments. Common order constants include xlAscending and xlDescending; header constants include xlYes, xlNo, and xlGuess. For predictable results, specify the header setting rather than asking Excel to guess.

VBA macros can rearrange workbook data, and sorting cannot be reversed with Ctrl+Z after a macro runs. Save a copy of the workbook before running any of the examples below. On Windows, VBA code runs in desktop Excel; Excel for the web cannot run VBA macros. Excel for Mac supports VBA, although macro security and some application details can differ.

Sort one column while keeping rows together

Usually, you do not want to sort one column independently: that would separate values from the rest of their records. Instead, sort the full data range and use a cell in the desired key column. This example sorts a four-column list by the values in column B, largest to smallest. It assumes row 1 contains headings and rows 2 through 100 contain records.

Sub SortBySales()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Sort each complete record by the value in column B.
    ws.Range("A1:D100").Sort _
        Key1:=ws.Range("B2"), _
        Order1:=xlDescending, _
        Header:=xlYes
End Sub

The call rearranges rows across columns A through D. It does not sort only the cells in column B. The key cell, B2, points to the field to use; Excel applies that field’s sort order to the rows in the specified range. If the data has no header, use Header:=xlNo and include every data row in the range.

To sort names alphabetically, change Order1 to xlAscending. The same basic pattern works for numbers, dates, and text, provided the cells contain values Excel can interpret consistently. Text that looks like a number may sort differently from an actual numeric value, a common issue covered below.

Sort by more than one column

Use the Sort object when you need a primary key and a tie-breaker, or want to set several options explicitly. Clear existing sort fields before adding yours; otherwise, a worksheet’s prior sort configuration can remain in place. Each key should describe the corresponding field in the data, and SetRange should cover the full records to move.

This example sorts a list first by department in column B, A to Z, then by sales in column C, highest to lowest. The heading is in row 1 and the data runs from rows 2 to 100.

Sub SortByDepartmentThenSales()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Define the primary and secondary sort fields.
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add Key:=ws.Range("C2:C100"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:D100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Excel groups matching departments together, then orders sales within each department. The sort-field keys exclude the header row, while SetRange includes it because .Header = xlYes identifies the first row as labels. Add further SortFields.Add calls for additional tie-breakers, in the order Excel should apply them. A third key, for example, is considered only when the first and second keys are equal.

Use a range that grows with your data

A fixed address such as A1:D100 is straightforward, but it misses later rows and may include empty rows if the list changes. For a simple contiguous list, find the last used row in a reliable key column, then build the sort range from that result. This example assumes column A is populated for every record and row 1 contains headings.

Sub SortGrowingList()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Find the last populated cell in the record ID column.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    Set sortRange = ws.Range("A1:D" & lastRow)
    ' Sort the complete list by column B, ascending.
    sortRange.Sort Key1:=ws.Range("B2"), _
        Order1:=xlAscending, Header:=xlYes
End Sub

The If check prevents a header-only list from being treated as a normal data set. The last-row method depends on the chosen column being filled for each record; if column A has gaps, it may stop too early. Choose a consistently populated column, or use an Excel table when records are regularly added. If the data spans multiple blocks or has notes below it, identify the intended range carefully rather than assuming the last used cell on the sheet marks the list’s boundary.

Headers, direction, and other sort settings

For a basic single-key sort, the settings most readers need are the key, direction, and header flag. In a multi-key sort, the Sort object exposes additional choices. Set options deliberately when the data’s layout or matching rules matter.

  • Header: use xlYes when the first row of the sort range contains headings, or xlNo when it contains data. xlGuess leaves the decision to Excel and can be ambiguous.
  • Orientation: xlTopToBottom sorts rows by columns, the usual arrangement for lists. xlLeftToRight sorts columns by rows and is for data laid out horizontally.
  • MatchCase: set to True if capitalization should affect text ordering; otherwise, use False.
  • SortOn: in a sort field, xlSortOnValues sorts cell values. Other sort behaviors are available for features such as cell or font color.
  • DataOption: xlSortNormal uses the normal interpretation of the values. xlSortTextAsNumbers is useful when numbers are stored as text and should be treated numerically.

These settings do not repair inconsistent source data. A column containing a mix of true dates, date-like text, blanks, and errors can still produce results that surprise readers. Normalize the values first if the intended order is important, then verify the result on a copy of the workbook.

Sorting Excel tables and filtered lists

Excel tables are often a better foundation than fixed ranges for lists that grow. A table expands as rows are added and carries its headers and structured data together. In VBA, a table is represented by a ListObject; its Sort property provides a sort object. You can build sort fields from the relevant table columns and apply the sort to the table rather than guessing its changing final row.

When working with a table, make sure the key belongs to the table column and that the intended operation sorts table records, not unrelated cells beside the table. The table’s filter dropdowns and Excel’s Data sort commands are also practical when a one-time sort is all that is needed. Sorting a filtered list can interact with the visible records and the underlying table; check the resulting records and retain a backup when the distinction matters.

A regular range with AutoFilter arrows is not necessarily the same object as an Excel table. If the data is just a range, specify the complete range and header setting explicitly. Avoid selecting only the key column when other columns hold related information.

Common problems and how to prevent them

Most VBA sorting errors come from a mismatch between the range, key, and data structure rather than from the sort command itself. Check these points when the macro runs but the output is wrong:

  • Rows became mismatched: the sort range may include only the key column. Expand it to cover every field in each record.
  • The heading moved into the data: set Header:=xlYes or .Header = xlYes when the first row is a header. Do not include a title row above the actual table unless it is handled separately.
  • New records were not sorted: replace a stale fixed endpoint with a last-row calculation or use a table that grows with its data.
  • The order seems alphabetical rather than numeric: some values may be numbers stored as text. Convert them to numbers or use an appropriate data option after checking the column’s contents.
  • The wrong worksheet was sorted: qualify ranges with a worksheet variable, as in ws.Range(...), instead of relying on whichever sheet is active.
  • Several sort keys behave unexpectedly: clear old sort fields, add the keys in priority order, and ensure SetRange includes every key column and every record.

Also check for merged cells or inconsistent row layouts in the target range. Sorting works best with a rectangular list where each row represents one record and each column represents one field. A macro should not silently assume that a worksheet’s current selection is the data set; explicit, qualified ranges make the operation easier to review and safer to repeat.

Range.Sort or the Sort object?

Use Range.Sort for a compact one-key operation with familiar defaults. Its named arguments keep the code short, and it is a good fit for a small macro that sorts a known block once. It can also accept additional keys and settings, but a long call becomes harder to read as requirements grow.

Use the Sort object for multiple keys, explicit sort options, or code that needs to manage sort fields systematically. Its steps are more verbose: clear fields, add them, set the full range and header behavior, then apply. That extra structure makes the priority order and configuration visible. Neither approach is universally better; choose the shortest one that expresses the actual sort reliably.

If a sort is needed only occasionally, use Excel’s Data > Sort command or a table’s header controls instead of writing a macro. For a formula-driven sorted view that leaves the original data untouched, Excel for Microsoft 365 and Excel 2021 also include the worksheet SORT function. That is a different tool from VBA’s Range.Sort: the function returns sorted results in the grid, while VBA rearranges the cells in place.

Frequently Asked Questions

What is the simplest VBA code to sort a range?
Use Range.Sort with a key, order, and header flag. For example, sort a complete range by one column with myRange.Sort Key1:=myKeyCell, Order1:=xlAscending, Header:=xlYes. Qualify both ranges with the worksheet for dependable results.

How do I sort by two columns in VBA?
Use a worksheet’s Sort object, clear its existing fields, add one SortFields.Add entry for each key in priority order, set the full range and header option, then call .Apply. The first field is the primary sort; later fields break ties.

Does VBA sort the whole row automatically?
Only within the range you tell Excel to sort. Include all columns belonging to each record in that range; otherwise, values outside it will not move with the key column.

Can I sort without a header row?
Yes. Set Header:=xlNo for Range.Sort, or .Header = xlNo for the Sort object, and define the range to include only the data you want reordered.

Can I undo a VBA sort with Ctrl+Z?
No. Running a macro clears Excel’s normal undo history, so Ctrl+Z will not restore the pre-sort order. Save a separate copy or preserve the original data before running a sorting macro.

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.