VBA Code to Sort Data in Excel: Examples and Tips

VBA & macros · Updated November 18, 2026

To sort Excel data with VBA, define the complete data range, add the column or columns to sort by, set the sort order, and apply the sort. The macro below sorts a table beginning in A1 by its second column in ascending order; adjust the worksheet name or key column to fit your data.

A basic VBA sort macro

This example assumes the worksheet is named Data, the table starts in A1, its first row contains headers, and the second column is the primary sort key. The table should be a contiguous block: there should not be a completely blank row or column splitting it. CurrentRegion identifies the connected block around A1, so it can include more or fewer rows than a fixed address such as A1:C100.

Sub SortDataBySecondColumn()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim keyRange As Range

    ' Use the worksheet that contains the table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' A header alone is not sortable data.
    If dataRange.Rows.Count < 2 Or dataRange.Columns.Count < 2 Then Exit Sub

    ' Use the second column as the key, excluding the header row.
    Set keyRange = dataRange.Columns(2).Offset(1, 0) _
        .Resize(dataRange.Rows.Count - 1, 1)

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

Before running a macro, save a copy of the workbook. Sorting changes the row order, and VBA actions cannot be undone with Ctrl+Z. To run the example, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, paste the code, and run the procedure while the workbook is open. On Mac, use Option+F11 (some keyboards require Fn as well) to open the editor; VBA is available in desktop Excel for Mac, though keyboard and dialog behavior can differ. Excel for the web cannot create, edit, or run VBA macros.

How the VBA sort works

Excel’s sort object needs to know both what to sort and how to sort it. The key range identifies the values that determine order; the full data range tells Excel which rows and columns must move together. If you sort only the key column, names, dates, and other values in the same records can become mismatched. Setting the whole table as .SetRange prevents that partial-sort problem.

  • .SortFields.Clear removes sort keys left over from an earlier sort operation.
  • .SortFields.Add adds a value-based key and sets ascending order. Use xlDescending for descending order.
  • .Header = xlYes tells Excel that the top row contains labels rather than a record to sort.
  • .Orientation = xlTopToBottom sorts records down the rows, which is the usual layout for a table.
  • .Apply carries out the configured sort.

The code uses a key range that excludes the header, while the complete range passed to .SetRange includes it. This makes the intended data rows explicit. The worksheet is named directly rather than using whichever sheet happens to be active, which helps prevent a macro from sorting the wrong sheet. Change “Data” to the exact tab name in your workbook, including spaces and punctuation.

Sort by a specific column or a fixed range

If your data is not a contiguous block beginning at A1, replace the CurrentRegion assignment with a range that matches the table. For example, a table with headers in B3:E50 can use Set dataRange = ws.Range("B3:E50"). For that layout, the sort key must refer to the appropriate column within the range. The first column of B3:E50 is column B, the second is C, and so on; choose the key range accordingly and keep the header setting at xlYes.

For a fixed range A1:C100 sorted by column B, a key such as B2:B100 is suitable, and the full A1:C100 range should be the sort range. Fixed addresses are straightforward but need maintenance when rows are added or removed. A dynamic range is more convenient when the table grows, provided its layout is consistent. If your records are an official Excel Table, you can also reference its named range in VBA; structured tables generally expand as rows are added, but the exact range reference depends on the table’s name and the intended sort key.

For a data set with no header row, set .Header = xlNo and include the first record in the key range. Do not choose this setting merely because the header looks like ordinary text: Excel will then treat the labels as sortable data and may move them into the records.

Sort by multiple columns

Many real lists need a primary and a secondary sort. For example, sort a staff list by department and then by surname. Add the primary key first and the secondary key after it. The second key only determines order among records tied on the first key. This complete example assumes headers in A1:C100, department in column A, surname in column B, and one record per row.

Sub SortByDepartmentThenSurname()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Change the sheet name and range to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:C100")

    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 dataRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Use the same row boundaries in the data range and each key range. If the table grows past row 100, update all three ranges or make the range dynamic. You can also set one key to descending and another to ascending; for example, sort by newest date first and then by customer name from A to Z. Each added key makes the sort rules more specific, so arrange them in order of importance.

Choose the right range and header setting

The most important design decision is defining the records to move together. A range that is too narrow can detach values from their associated records. A range that is too broad can pull unrelated notes or another table into the sort. Inspect the worksheet layout before writing the macro, and decide whether blank rows, totals, or footnotes belong inside the sortable block.

  • Use a known address when the data location and size are fixed and controlled.
  • Use CurrentRegion for a simple, connected block whose top-left cell is known.
  • Use a named Excel Table when the list expands and should retain table behavior, such as filters and calculated columns.
  • Choose xlYes for a header row and xlNo when every row is data.

CurrentRegion stops at completely blank rows or columns. If a blank row separates the header from records, or a blank column separates related fields, it may return only part of the intended data. Conversely, a nearby connected note can be included if it touches the data block. For irregular worksheets, explicitly identify the last row and column or use an Excel Table rather than assuming the region is correct.

Common sorting problems and fixes

A macro can run without an error and still produce an unexpected order. Often the cause is the data itself rather than the sort command. Check a few representative values manually, especially when a column contains mixed formats or imported content.

  • Rows no longer match: The sort range may cover only the key column. Set the range to include every column in each record.
  • The header moves: The header setting may be xlNo, or the range may start below the actual header. Correct both the range and the setting.
  • Numbers sort strangely: Some entries may be stored as text. A text value such as “100” can be ordered differently from numeric 20. Convert inconsistent values to a consistent data type before sorting.
  • Dates appear out of order: Text that looks like a date may not be a real Excel date. Check the cell values and regional date interpretation, then convert the column consistently.
  • Only part of the list is sorted: A blank row or column may have caused CurrentRegion to stop early. Inspect the range the macro identifies or use an explicit range.
  • The sort is reversed or unexpected: Verify the key column and whether its order is xlAscending or xlDescending. Also check for leading spaces and inconsistent spelling.

Merged cells can interfere with sorting because the merged layout may not align across the selected range. Unmerge cells in the data area where possible, and repeat labels down rows instead of merging them. Also avoid including a totals row as an ordinary record unless you deliberately want it sorted with the data.

VBA sorting compared with Excel’s Sort command

For a one-time sort, Excel’s built-in command is usually quicker: select a cell in the data, choose Data > Sort, select the column, and specify the order. In the Sort dialog, confirm that the data has headers when appropriate and use Add Level for secondary keys. Excel’s command is useful when a person needs to review the chosen keys before applying them.

VBA is useful when the same sort must run repeatedly, when it is part of a larger workflow, or when a button or other macro should apply a consistent rule. It also makes the key order and range explicit in code. A macro is not automatically safer or more accurate than the ribbon command: it will repeat whatever assumptions were programmed, including a mistaken range or header setting. Excel’s dynamic array SORT function is another option in Microsoft 365 and Excel 2021 or later when a separate sorted result is preferable to rearranging the original records. Unlike a VBA sort, a formula result does not move the source rows.

Macro safety and workbook compatibility

Save a backup before running a sort macro, particularly if the workbook contains formulas, linked records, or manually arranged sections. A VBA sort is not reversed by Ctrl+Z, so a backup or a deliberate way to restore the original order is important. If original order matters, add an index column before sorting; you can sort by that column later to restore the sequence.

Excel may block macros in files downloaded from the internet or received by email. Only enable macros when you trust the workbook and its source. Save macro-enabled workbooks in the Excel Macro-Enabled Workbook format, .xlsm; a standard .xlsx workbook cannot store VBA code. VBA macros run in desktop Excel for Windows and Mac, but not in Excel for the web. The ribbon may differ slightly across desktop releases and operating systems, but the core sort object model shown here is intended for Excel VBA.

Frequently Asked Questions

How do I sort data in Excel VBA from A to Z?
Set the key column in SortFields.Add and use Order:=xlAscending. Set .SetRange to the full table and choose the correct header setting before applying the sort.

How do I sort largest to smallest with VBA?
Use Order:=xlDescending for the relevant sort field. This sorts numbers from high to low and text in reverse alphabetical order, subject to how Excel interprets the cell values.

Can I sort a range without a header row?
Yes. Set .Header = xlNo and include the first data row in the key range. If the range does have column labels, use xlYes so they stay at the top.

Why does my VBA sort separate values from their rows?
The sort range probably contains only the key column or omits some fields. Expand .SetRange to include every column that belongs to each record.

Can I run an Excel VBA sort in the browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, or use a supported alternative such as a manual sort or an appropriate worksheet formula.

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.