Sort Columns in Excel with VBA

Yes. In Excel VBA, use the Sort object to sort entire rows by values in a chosen column, or set its orientation to sort columns from left to right. The key distinction is whether you want records rearranged by one field or whole worksheet columns reordered; sorting only one field can separate values that belong together.

What “sort columns” can mean

People use “sort columns” to describe two different operations in Excel. The first, and more common, is sorting a table by a column: for example, ordering customer records by last name or transactions by date. Excel changes the order of the rows, while the selected column supplies the sort key. The second is sorting columns themselves from left to right, perhaps to arrange monthly data according to values in a particular row.

Before writing a macro, decide which operation you mean and identify the complete range that should move. If each row represents one record, include every field in that record in the sort range. If each column represents one item and the items should be rearranged horizontally, include all the rows that belong to those items. A correct sort key does not protect data that was left outside the range.

Sort complete rows by a column

This example sorts a table on a worksheet named Data by the values in column B, ascending. The header is in row 1, and the data occupies A2:C100. The full range A1:C100 is sorted, so each row’s values stay together.

Sub SortRowsByColumnB()
    Dim ws As Worksheet
    Dim sortRange As Range

    ' Set the worksheet and the complete table, including its header.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sortRange = ws.Range("A1:C100")

    With ws.Sort
        .SortFields.Clear
        ' Column B supplies the key; exclude its header from the key range.
        .SortFields.Add Key:=ws.Range("B2:B100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Change the worksheet name, range, and key range to match the workbook. For example, if the table runs from A1:F250 and the sort field is column D, use A1:F250 for sortRange and D2:D250 for the key. Keeping the header out of the key range while including it in the overall sort range makes the intended header row explicit.

Run the macro safely

Save a separate copy of the workbook before running a sorting macro. VBA changes made by a macro cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if the range or sort order is wrong. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its VBA code.

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. On Mac, use Option+F11; depending on the keyboard, you may need Fn+Option+F11. You can also open the editor from the Developer tab if it is available.
In the editor, choose Insert > Module, then paste the procedure into the new module.
Change the worksheet name and range in the code so they match your data. Check that the sort key is inside the sort range.
Return to Excel, press Alt+F8 on Windows, select SortRowsByColumnB, and choose Run. On Mac, use the Macro dialog or run the procedure from the Visual Basic Editor.

Excel may show a security warning when opening a workbook containing macros. Enable macros only when you trust the workbook and its source. A macro does not need to be run every time you open Excel; you can run it when the data needs sorting.

Sort descending or use multiple keys

For descending order, replace xlAscending with xlDescending. The setting applies to that sort field: for example, dates can be ordered newest to oldest, or numbers from largest to smallest. Text sorts alphabetically in ascending order and in reverse alphabetical order when descending, subject to Excel’s sorting rules and the data’s contents.

To sort by more than one field, add additional sort fields before setting the range and applying the sort. Excel applies the first field as the primary order, then uses the next field to order records tied on the first. For instance, sort a staff list by department and then by surname:

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

    Set ws = ThisWorkbook.Worksheets("Staff")
    Set sortRange = ws.Range("A1:D200")

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

In this example, column B is the primary key and column A breaks ties. Include every relevant field in the sort range, even if it is not a sort key. If the number of records changes regularly, a fixed endpoint such as row 200 may omit new records or include unused rows. For a changing dataset, consider using an Excel Table and sorting its data body, or calculate the last populated row before building the range.

Sort columns from left to right

To rearrange columns horizontally based on values in a row, set the orientation to xlLeftToRight. This example sorts the columns A through F according to the values in row 2, moving the full A1:F10 block horizontally. The row used as the key is within the overall range.

Sub SortColumnsByRow2()
    Dim ws As Worksheet
    Dim sortRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sortRange = ws.Range("A1:F10")

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A2:F2"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlLeftToRight
        .Apply
    End With
End Sub

Here, xlNo means the range is treated as data rather than as a table with a header row. If row 1 contains labels that should move with their columns, include it in the range as shown. Inspect the result on a copy, especially if the leftmost column contains row labels that should stay fixed: including that column means it can move too. Adjust the range to include exactly the columns that should be rearranged.

Choosing a range and handling headers

The most important decisions are the sort range, key range, and header setting. The sort range is all the cells that should move together. The key range contains the values Excel compares to determine order. The header setting tells Excel whether the first row of the range is a label or data; an incorrect setting can leave a header among the sorted records or exclude an actual record from the expected order.

Use a complete rectangular range. If names are in A and phone numbers are in C, include both columns and any fields between them when sorting the records.
Keep the key within the sort range. A key outside the selected block cannot reliably describe the records being moved.
Set the header deliberately. Use xlYes when the first row of the range contains field names and xlNo when it contains ordinary data.
Check for gaps and side-by-side data. A blank column may cause Excel’s automatically detected region to stop earlier than intended. Explicit ranges or Tables make the target clearer.

Excel Tables are often a good fit for lists that grow. You can refer to a table by its name in VBA rather than hard-coding the last row, then sort the table’s data range so new rows are included. A table also identifies its header separately from its records. If you use a fixed range instead, update both the sort range and the key range consistently whenever the list expands.

Common problems and practical checks

If a macro produces an unexpected order, first verify that the correct worksheet is active in the code. Using an explicit worksheet variable, as in the examples, avoids relying on whichever sheet happens to be selected. Then check the start and end rows, whether the header setting matches the range, and whether all columns that belong to each record are included.

Rows no longer match: The macro may have sorted only the key column. Sort the full record range, not just the one field.
Some records did not move: The defined range may end too early, or a blank row may separate part of the list.
Numbers or dates appear out of order: Some entries may be stored as text while others are real numbers or dates. Standardize the underlying data before sorting; changing the sort order alone does not convert text into numeric values.
A heading appears in the data: Check that the first row is included in the sort range and that .Header is set to xlYes.
The macro reports an error: Confirm that the worksheet name exists exactly as written and that the specified ranges are valid for that sheet.

For custom orders, such as weekdays in calendar order rather than alphabetical order, a basic ascending sort may not give the desired result. Excel supports custom lists, but the setup is different from a simple alphabetical or numeric sort. Consider defining the intended order explicitly and checking the behavior on a small copy of the data before applying it to a production workbook.

VBA sorting compared with manual sorting

For a one-time sort, Excel’s ribbon is usually quicker: select a cell in the data and use Data > Sort, then choose the column and order. The dialog is useful for setting multiple levels and confirming whether the range has headers. For a recurring task, VBA can apply the same specified range, keys, and directions consistently, and it can be called from another macro.

VBA is not automatically safer or more accurate than the ribbon. A macro repeats its instructions, including mistakes in its ranges. Manual sorting gives you a chance to inspect the dialog each time, while code is valuable when the sorting rules are stable and the process is documented. On Excel for the web, you can use the workbook’s available sorting controls, but you cannot create, edit, or run VBA macros there; use desktop Excel for VBA.

Frequently Asked Questions

Can I sort just one column in Excel with VBA?
Yes, but do so only when that column is independent. If its cells correspond to other values in the same rows, sorting it alone breaks those relationships. For a table or list of records, set the sort range to the entire group of related columns.

How do I sort a column from Z to A?
Use the desired column as the key and set its sort order to xlDescending. In the row-sorting example, replace Order:=xlAscending with Order:=xlDescending.

Can VBA sort by more than one column?
Yes. Clear the existing sort fields, add the primary key first, then add secondary keys in priority order before applying the sort. Excel uses later keys to order records that tie on earlier keys.

Does sorting with VBA work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app for Microsoft 365 on Windows or Mac to run VBA; sorting through the web app’s regular Excel controls is a separate option.

Can I undo a VBA sort with Ctrl+Z?
Generally, no. Running a macro clears Excel’s normal undo history, so Ctrl+Z cannot be relied on to reverse the sort. Save a copy before running code, or make the macro operate on a duplicate sheet if you need to preserve the original order.

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.