VBA Sort Column: Code, Options, and Common Fixes

VBA & macros · Updated November 18, 2026

To sort a column with VBA in Excel, define the sort key and apply a sort to the full data range: use the column you want to order as the key, and include all related columns in the range so each row stays together. The example below sorts rows A:C by column B, ascending, with a header row.

What a VBA column sort does

A sort key is the column Excel uses to decide the order of records. If column B contains sales dates, for example, sorting by B rearranges the selected rows according to those dates. The other values on each row move with them only when the sort range includes those columns.

This distinction matters. If a customer name is in column A and the matching order amount is in column C, sorting only column B can separate values that belong to the same record. For a table of related information, sort the entire rectangular data range by one key column. Sorting just one column is appropriate only when its values are an independent list with no row relationships to preserve.

Excel VBA provides a worksheet Sort object with sort fields, a range to sort, and settings such as header handling and direction. You can also use the Range.Sort method for a compact one-key sort. The Sort object is useful when you need clearer control or multiple sort keys.

Sort a data range by one column

This macro sorts A1:C through the last used row, using column B as the ascending key. It assumes row 1 contains headings and that column B is filled for every data record. Replace Sales with the name of your worksheet, and adjust the range and key column to suit your layout.

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

    ' Use the worksheet in the workbook that contains this macro.
    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Find the last populated row in the sort-key column.
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Do not try to sort a header with no data beneath it.
    If lastRow < 2 Then Exit Sub

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

The key starts at B2 rather than B1 because row 1 is a heading. SetRange includes the heading and all associated data in columns A through C. Header set to xlYes tells Excel not to treat the heading as a record. xlAscending puts lower values first; use xlDescending to reverse the order. The macro finds its last row from column B, so choose a key column that is populated through the records you intend to sort.

Put the macro in Excel and run it

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the module, and change the worksheet name and range as needed. Return to Excel and run it with Developer > Macros, select SortRowsByColumnB, and choose Run. If the Developer tab is hidden, enable it in File > Options > Customize Ribbon.

On Excel for Mac, the VBA editor is available from Tools > Macro > Visual Basic Editor, or from the Developer tab if it is shown. Option+F11 is a common editor shortcut; some Mac keyboards require Fn as well. You can run the macro from Tools > Macro > Macros or the Developer tab. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop application.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code. Excel may require you to allow macros, depending on the file source and your security settings. Only enable macros in workbooks you trust.

Sort a standalone column without moving other columns

If the values really are an independent list, you can sort only that range. For example, this procedure orders B2:B20 from smallest to largest and leaves every other column alone:

Sub SortStandaloneList()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Sort only the independent list; the heading is not included.
    ws.Range("B2:B20").Sort Key1:=ws.Range("B2"), _
        Order1:=xlAscending, Header:=xlNo
End Sub

Here the range contains no heading, so Header is xlNo. If the selected range included a heading, use xlYes and point Key1 at the first data cell in the key column. A frequent mistake is to apply this kind of single-column sort to one field in a multi-column record table. Excel cannot infer which neighboring cells belong with each value; select the full record range instead.

Choose ascending, descending, and header settings

Use xlAscending for A to Z, earliest to latest, or smallest to largest; use xlDescending for the opposite direction. For text, the result follows Excel’s sorting rules and the workbook’s locale. For dates and numbers, the cells need to contain real date or numeric values. Text that looks like a number may sort differently from a number, so check the cell values if the result seems unexpected.

  • .Header = xlYes: the first row of the selected range contains column labels.
  • .Header = xlNo: the selected range contains data only.
  • .MatchCase = True: Excel distinguishes uppercase and lowercase text when sorting; False does not request case-sensitive sorting.
  • .Orientation = xlTopToBottom: sort records down rows, the usual arrangement for a list or table.

If you omit or misstate the header setting, Excel can sort the heading into the data. A heading that looks like an ordinary value may be especially hard to notice afterward. Specify the setting rather than relying on Excel to guess.

Sort by more than one column

For ties in the main key, add another sort field. For instance, to sort a staff list by department and then by surname, add the department field first and the surname field second. In the SortFields collection, the earlier field has higher priority. Each key must sit inside the range supplied to SetRange.

In the first example, add these calls after SortFields.Clear and before SetRange, changing the ranges to match the worksheet:

.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal

This example assumes departments are in column A and surnames in column B, with the complete records still included in SetRange. Excel groups by department first, then orders records within each department by surname. For a different priority, reorder the SortFields.Add calls. You can also set a secondary key to xlDescending while keeping the primary key ascending.

Find the correct range and handle changing data

A fixed range such as A1:C100 is straightforward but can omit newly added rows or include unused cells. The sample calculates lastRow each time it runs, which helps when the list grows or shrinks. Its End(xlUp) method searches upward from the bottom of the worksheet in column B. If column B has gaps near the bottom, this still finds the last nonblank cell in B, but it cannot detect records below that point whose key cells are blank.

Choose the column used to find the last row carefully. If no single column is reliably filled, use a known data boundary, a properly maintained Excel Table, or logic that checks more than one column. A table can be more convenient for expanding data because it tracks its rows and headings. When sorting a table, use its own sort controls or its ListObject.Sort object rather than accidentally sorting only part of the table. Make sure the selected sort keys and range correspond to the table’s actual columns.

Also confirm that the sort range does not include unrelated notes or a second list below the main data. A correct key cannot compensate for an incorrect range: Excel will rearrange everything inside SetRange as records.

Common VBA sorting problems

  • Rows become mismatched: the sort range covered only the key column. Expand SetRange to include every field belonging to each record.
  • The heading moves: set .Header to xlYes when the first row of SetRange is a heading, and ensure the key range begins below it.
  • New rows remain unsorted: the last-row calculation is based on a column that is blank for those rows, or the sort range is hard-coded. Choose a more dependable boundary.
  • Numbers or dates sort oddly: some cells may be stored as text, or may contain inconsistent values. Check their underlying cell types and formatting; changing display format alone does not necessarily convert text to numbers or dates.
  • A previous sort seems to affect the next one: clear the worksheet’s existing sort fields with .SortFields.Clear before adding the fields for the current operation.
  • The macro cannot find the sheet: the worksheet tab name must match the string in Worksheets exactly. ThisWorkbook refers to the workbook containing the code, which may not be the active workbook.

Sorting changes the order of cells in the worksheet. It is not undone by pressing Ctrl+Z after a macro runs, and the macro does not provide Excel’s normal undo history. On Windows, save a copy of the workbook before running a sorting macro; do the same on Mac. This is especially important when the range is large or the macro will run automatically.

VBA sorting compared with Excel’s built-in sort

For a one-time sort, Excel’s Data > Sort command is often simpler and lets you inspect the sort levels before applying them. VBA is useful when the same sort must run repeatedly, be triggered by another procedure, or be applied consistently across workbooks. A macro can also reduce manual mistakes by explicitly setting the key, range, direction, and header behavior.

Excel worksheet formulas such as SORT can return a sorted array in supported Microsoft 365 and Excel 2021 versions, but they do not rearrange the original source cells. A formula-based result can be useful when you want a separate, automatically updated view. VBA changes the order of the selected worksheet cells. Choose based on whether you need a refreshed output or a reordered data range.

Frequently Asked Questions

How do I sort a column in VBA from A to Z?
Set the key to that column’s data cells, set Order to xlAscending, and apply the sort to the full related data range if rows must stay together. Use xlYes for a range that includes a header.

How do I sort a column in descending order?
Change the sort field’s Order value from xlAscending to xlDescending. In a Range.Sort call, use Order1:=xlDescending for the first key.

Can VBA sort a column without moving the rest of the row?
Yes. Sort only that column’s range, but do so only if its values are independent. If the other cells on each row describe the same record, include those columns in the sort range to keep the row intact.

What does Header:=xlYes mean?
It tells Excel that the first row of the selected sort range contains labels rather than sortable records. Use xlNo when the selected range contains data only.

Can I sort an Excel Table with VBA?
Yes. A table has a ListObject.Sort object that can sort its rows by table columns. Use the table’s sort object and the appropriate table-column key rather than selecting only one worksheet column, so the table’s records remain together.

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.