Excel VBA Macro to Sort Data: How to Write One

VBA & macros · Updated November 22, 2026

The quickest way to sort data with an Excel VBA macro is to record a sort and then edit the generated code, or to write a short macro using the Range.Sort method. For example, to sort cells A2:A20 by the values in column A, you can use: Range("A2:A20").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo. Below is a complete guide to writing, customizing, and troubleshooting VBA sort macros.

Why Use a VBA Macro to Sort Data?

Excel’s built-in sort (Data tab > Sort) works fine for one-time sorting. But a macro becomes valuable when you sort the same data repeatedly, need to sort multiple sheets in one go, or want to automate sorting as part of a larger workflow (e.g., after importing data). A VBA macro can also apply a consistent sort order every time, reducing the risk of human error.

How the Range.Sort Method Works

The Sort method is applied to a range object. The essential parameters are:

  • Key1 – the column (or row) that determines the sort order.
  • Order1 – xlAscending (A to Z, smallest to largest) or xlDescending (Z to A, largest to smallest).
  • Header – xlYes if the first row contains headers, xlNo if not.

You can also specify a second sort key with Key2 and Order2, and a third with Key3. The Sort method sorts the entire range you specify, so if you include all columns in the range, they move together.

Writing a Simple Sort Macro

Here’s a complete macro that sorts a table with headers (A1:D20) by column B (e.g., a “Last Name” column) in ascending order:

Sub SortByColumnB()
    ' Sorts A1:D20 by column B, assuming row 1 has headers
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ws.Range("A1:D20").Sort _
        Key1:=ws.Range("B2"), _
        Order1:=xlAscending, _
        Header:=xlYes
End Sub

To run this, press Alt+F8 (on Windows) or Option+F8 (on Mac), select SortByColumnB, and click Run. Always save a copy of your workbook before running any macro, because macros cannot be undone with Ctrl+Z.

Recording a Sort Macro: The Easiest Way

If you’re not comfortable writing code, use the macro recorder:

  1. Select the data range (including headers).
  2. Go to the View tab > Macros > Record Macro.
  3. Give the macro a name (e.g., SortData) and click OK.
  4. Go to the Data tab > Sort, set your sort criteria, and click OK.
  5. Go back to View > Macros > Stop Recording.

The recorded code will use the Sort method with your exact settings. You can then open the VBA editor (Alt+F11) to view and modify it. Note that the recorder often uses SortFields and Apply—this is just a longer way of doing the same thing.

Sorting by Multiple Columns

To sort by more than one column, add Key2 and Key3. For example, to sort by Last Name (column B) and then by First Name (column A):

Sub SortByTwoColumns()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ws.Range("A1:D20").Sort _
        Key1:=ws.Range("B2"), Order1:=xlAscending, _
        Key2:=ws.Range("A2"), Order2:=xlAscending, _
        Header:=xlYes
End Sub

Remember that the range must include all columns you want to move together. If you only sort a single column, the rest of the data will not reorder—a common mistake.

Common Problems and How to Fix Them

  • Data not sorting correctly: Make sure the range includes all columns and that the header row is correctly identified. If your data has no headers, set Header:=xlNo.
  • Sorting only one column: If you select only a single column in the Sort method, only that column is sorted. Always select the full data range.
  • Numbers sort as text: If numbers are stored as text, they will sort alphabetically (e.g., 10 before 2). Convert them to numbers using the Value property or the Text to Columns feature.
  • Macro runs but nothing happens: Check that the worksheet name in the code matches your actual sheet name. Also, ensure the range references are correct.
  • Error “Sort reference is not valid”: This often occurs when the Key1 range is not within the range being sorted. Ensure the key cell is inside the sort range.

Sorting a Whole Sheet or a Dynamic Range

To sort an entire sheet (all used rows and columns), you can use ws.UsedRange. For a range that changes size, use CurrentRegion if the data is contiguous:

Sub SortDynamicRange()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Assumes A1 is the top-left cell of a contiguous data block
    ws.Range("A1").CurrentRegion.Sort _
        Key1:=ws.Range("A2"), _
        Order1:=xlAscending, _
        Header:=xlYes
End Sub

CurrentRegion expands to include all adjacent cells with data, so it will automatically include new rows or columns as long as there are no blank rows or columns within the data.

Frequently Asked Questions

Can I sort by row instead of column?

Yes. The Sort method has an Orientation parameter. Use Orientation:=xlSortRows to sort left-to-right instead of top-to-bottom. For example, to sort rows 1 to 5 by the values in row 1, you’d write Range("A1:E5").Sort Key1:=Range("B1"), Orientation:=xlSortRows.

How do I sort with a custom order (e.g., High, Medium, Low)?

Use the CustomOrder parameter. For example: Sort Key1:=Range("A2"), Order1:=xlAscending, CustomOrder:="High,Medium,Low". The custom list must be comma-separated and match the cell values exactly.

Why does my macro sort only the selected column?

Because you likely specified a single-column range in the Sort method. Always include the entire data range (all columns) in the range you are sorting, even if the key is only one column.

Can I undo a sort done by a macro?

No. Macros cannot be undone with Ctrl+Z. That’s why it’s critical to save a backup copy of your workbook before running any sorting macro. You can also add a line at the start of the macro to save a copy automatically, but that’s beyond the basics.

Does the macro work on Excel for Mac?

Yes, VBA is supported on Excel for Mac. The code is identical, but the keyboard shortcuts differ (e.g., Alt+F11 is Option+F11). Be aware that some advanced features may behave slightly differently, but the Sort method works the same.

Alternatives to VBA for Sorting

If you don’t want to use VBA, consider these built-in alternatives:

  • Sort buttons: Select a cell in the column and click the A→Z or Z→A button on the Data tab.
  • Sort dialog: Data > Sort gives you multiple levels and custom orders.
  • SORT function: In Excel 365, =SORT(array, [sort_index], [sort_order], [by_col]) returns a sorted array without changing the original data. This is a formula-based approach that updates automatically.

VBA is best when you need to automate sorting as part of a larger task, such as cleaning a report every morning. For one-off sorting, the built-in tools are simpler and safer.

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.