VBA Excel Sort Values: Examples, Options, and Fixes

VBA & macros · Updated November 21, 2026

To sort Excel values with VBA, use the worksheet’s Sort object: set the data range, choose the column to sort by, specify ascending or descending order, and apply the sort. The example below sorts records in columns A:B by the values in column B, while keeping each row together.

What sorting with VBA does

Excel’s sort feature rearranges cells according to the values in one or more columns. In VBA, you can automate that operation with a range’s Sort method or, for more control, the worksheet’s Sort object. The object is useful when you need to declare a range, identify a header row, and set sort options explicitly.

Sorting by values does not mean that Excel extracts values and sorts them separately from the rest of the data. When you sort a table or rectangular range, Excel moves entire rows so related information stays together. For example, sorting a sales list by the amount in column B also moves the customer name in column A on the same row. If you sort only column B by itself, names and amounts can become mismatched.

Excel can sort numbers, text, dates, and formula results. A formula cell is sorted according to its calculated value, while its formula travels with its row when the whole data range is sorted. This distinction matters when a workbook contains linked records or formulas that refer to other cells.

A basic VBA macro for sorting a range

This macro assumes row 1 contains headings, column A contains a value on every data row, and column B contains the sort key. It finds the last used row in column A, then sorts the full two-column range by column B from smallest to largest.

Sub SortRecordsBySales()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the active sheet; confirm it is the intended worksheet.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if there are no data rows beneath the header.
    If lastRow < 2 Then
        MsgBox "There are no data rows to sort."
        Exit Sub
    End If

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:B" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

The Key argument identifies the cells that determine the order; SetRange identifies all the cells that should move. Those ranges must align row for row. Here the key starts at B2 because row 1 is a header, while the full range starts at A1 so the heading is included and identified by .Header = xlYes.

To use it, open the VBA editor with Alt+F11 on Windows, insert a standard module with Insert > Module, and paste the procedure. Run it with Alt+F8 and select SortRecordsBySales. On Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. Excel for the web does not run VBA macros, so use desktop Excel for this procedure.

Choosing ascending or descending order

The order is set by the Order property on the sort field. Use xlAscending for A to Z, oldest to newest, or low to high. Use xlDescending for Z to A, newest to oldest, or high to low. For the macro above, change Order:=xlAscending to Order:=xlDescending to reverse the sort. The sort key remains column B in either case.

For numbers, ascending means the smaller values appear first; it does not mean sorting the digits as text. Text and numbers stored as text can behave differently, especially if a column mixes types. Dates should be genuine Excel date values if you want chronological ordering. A date-looking text string may sort alphabetically instead of by date, so check the underlying cell values if the result looks unexpected.

Excel also supports options such as case sensitivity and custom lists through its sort settings. The example sets .MatchCase = False, meaning capitalization does not control the order. If you need an order such as High, Medium, Low rather than alphabetical order, use a custom list in Excel’s sort dialog or add a helper column that assigns each category a numeric rank.

Sorting complete rows and handling headers

The safest general rule for a list is to sort the full rectangular data range, not just the key column. If a table has names in A, departments in B, and scores in C, set the range to all three columns and use the score column as the key. That keeps each person’s department and score attached to their name.

Set .Header = xlYes when the first row contains field names, and use xlNo when every row in the range is data. The macro makes this choice explicitly rather than asking Excel to guess. A wrong header setting can cause the heading to be sorted into the records or leave the first data row out of the intended order.

For a formatted Excel table, sorting through the table’s data range can be more robust as the table grows. The example uses a normal worksheet range and determines its endpoint from column A. That approach assumes column A is populated for every record. If it can contain blanks, determine the last row from a more consistently filled column, or use the table’s named range and its data body. A last-row calculation based on the wrong column can omit records at the bottom.

Sorting by more than one column

Multi-level sorting applies a primary key first and a secondary key to records tied on that first key. For example, sort employees by department and then alphabetically by surname. Add each key to SortFields in priority order, and set the range to include every relevant column.

Sub SortEmployeesByDepartmentAndName()
    Dim ws As Worksheet

    Set ws = ActiveSheet

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A2:A100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add Key:=ws.Range("B2:B100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:C100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

This example assumes headings in row 1, departments in A, surnames in B, and records through row 100. The first added field is the primary sort, and the second breaks ties. Adjust the row numbers and columns to fit the actual list. For different directions, set the order separately for each key; for instance, department could be ascending while a score key is descending.

Common sorting problems and how to fix them

  • Only one column moves. Expand .SetRange to cover the entire record, not just the sort column. Otherwise, the values can become associated with the wrong rows.
  • The heading gets mixed into the data. Check that the range begins on the heading row and that .Header = xlYes. If the range begins on the first data row, use xlNo instead.
  • Some records are missing. Check how lastRow is calculated. The example looks down column A, so a blank in A at the end of the list can make the detected range too short.
  • Numbers or dates sort strangely. Look for numbers stored as text, inconsistent date formats, leading or trailing spaces, and mixed data types. Clean or convert the source values before sorting when consistent order is required.
  • The macro sorts the wrong sheet. ActiveSheet refers to whichever worksheet is active when the macro runs. For a fixed workbook, assign ws to a named worksheet instead, and confirm the sheet name exists.
  • The macro does not run. Save the file as an Excel Macro-Enabled Workbook (.xlsm), use desktop Excel, and allow macros only when the file is trusted. Excel’s security settings may block macros from downloaded files.

Before running a sorting macro, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, and a mistaken range or key can rearrange data in a way that is difficult to restore. On Mac, use Command+Z for ordinary undo, but it does not restore a VBA sort either.

VBA sorting compared with other options

For a one-time sort, the ribbon is usually quicker: select a cell in the data, then use Data > Sort to configure one or more levels. The A to Z and Z to A buttons are convenient for simple sorts, but confirm that Excel is sorting the full list and recognizes the header. VBA is useful when the same operation must run repeatedly, as part of a larger macro, or after data is refreshed.

For a formula-driven sorted result that updates when source data changes, Excel’s SORT function is an alternative in Microsoft 365 and Excel 2021 or later. It returns a sorted array without rearranging the original cells, which is useful when the original order must remain intact. VBA instead changes the order of the cells in the target range. Choose based on whether you need to rearrange the source records or display a separate sorted view.

Macros also require a macro-enabled file and appropriate security permissions, while a formula can often be shared in a regular workbook. Excel for the web can display many workbook features but cannot execute VBA. If the workbook must be automated in the browser, consider Office Scripts where available, or use a formula or manual sort; VBA code itself is not interchangeable with an Office Script.

Frequently Asked Questions

How do I sort a column from largest to smallest in VBA?
Set the sort field’s order to xlDescending. Sort the full data range if the values belong to records in other columns, so the complete rows stay together.

Can VBA sort a range without a header?
Yes. Set .Header = xlNo and make sure the range begins at the first data row. That tells Excel not to treat its first row as a heading.

Does sorting with VBA change the formulas?
Sorting a full range moves formula cells with their rows. Excel may adjust relative references according to normal worksheet behavior. If the formulas depend on a particular layout, save a copy and verify the results after sorting.

Can I sort only the values and leave the other columns untouched?
You can sort a single column alone, but that separates its values from the records in neighboring columns. Do so only when the column is independent; otherwise, sort the entire record range by that column.

Can an Excel macro be undone with Ctrl+Z?
No. Running VBA clears Excel’s ordinary undo history, so Ctrl+Z will not reverse the sort. Save a backup copy before running a macro, particularly when the range or sort key is being changed.

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.