VBA Sort in Two Columns: Code, Steps, and Key Options

VBA & macros · Updated November 18, 2026

Yes. In Excel VBA, sort the complete data range and pass two keys to its Sort method: the first key sets the main order, and the second breaks ties. For example, the macro below sorts a table by column A and then column B, both ascending.

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

    ' Change Data to the name of the worksheet to sort.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    If dataRange.Rows.Count < 2 Then Exit Sub

    ' Sort the entire connected data range, not just the key columns.
    dataRange.Sort _
        Key1:=dataRange.Cells(2, 1), Order1:=xlAscending, _
        Key2:=dataRange.Cells(2, 2), Order2:=xlAscending, _
        Header:=xlYes, Orientation:=xlTopToBottom
End Sub

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The example assumes headers in row 1, records below them, and a connected data block starting at A1. The sections below explain how to adapt it, avoid common sorting mistakes, and choose the right approach for your worksheet.

What “sort in two columns” means

A two-column sort uses one column as the primary key and another as a secondary key. Excel sorts by the first key, then sorts records that tie on that key using the second. Suppose a list has Department in column A, Employee in B, and Start Date in C. Sorting by Department and then Employee groups departments together and alphabetizes names within each group. The date column moves with its employee’s row, even though it is not a sort key.

This is different from sorting column A and then independently sorting column B. Independent sorts can separate values that belong together. In a proper multi-key sort, Excel rearranges entire rows in the selected range, preserving each record. You can use more than two keys as well, but two are often enough for grouping plus ordering within a group.

How the VBA sort code works

dataRange.Sort applies a sort to the range as a whole. Key1 identifies the primary sort column; Order1 sets its direction. Key2 and Order2 define the secondary sort. In the example, the key references point to cells in the second row of the data range, which is the first record row beneath the header. The key cells identify the relevant columns; Excel sorts the rows in the full range.

Header:=xlYes tells Excel that the first row contains field names, not records to rearrange. Orientation:=xlTopToBottom specifies a normal row-based sort. The check for fewer than two rows exits when there is no record to sort; it also avoids attempting a sort on a header-only range.

The sheet name in Worksheets("Data") must match the tab name. If your sheet is called “Staff,” change that string to Worksheets("Staff"). The macro uses ThisWorkbook, meaning the workbook that contains the VBA code. If the code is stored in a personal macro workbook but should sort the active workbook, you would need to reference that workbook deliberately rather than assume it is ThisWorkbook.

Set up and run the macro in Excel

  1. Arrange the records in a rectangular block with one header row and no completely blank rows or columns splitting the block. Put the primary key in the first column of the example range and the secondary key in the next column.
  2. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
  3. Open the Visual Basic Editor with Alt+F11 on Windows. On Mac, use Option+F11; depending on keyboard settings, you may also need the Fn key.
  4. In the editor, choose Insert > Module, paste the code, and change the worksheet name if necessary.
  5. Return to Excel, save a backup copy, then run the macro from the editor with the cursor inside the procedure and F5. You can also run it from Developer > Macros in Excel for Windows. On Mac, the Developer tab may need to be enabled in Excel settings, or use Tools > Macro > Macros.

When a file downloaded from the internet contains macros, Excel may block them under its security settings. Enable only code from a source you trust; do not weaken macro security globally just to run an unfamiliar workbook. Excel for the web can open and edit many workbooks, but it cannot run VBA macros. Use the desktop Excel application to execute this code.

Choose the range carefully

The sample uses CurrentRegion, which finds the connected block around A1. This is convenient when the data is contiguous and its top-left header is in A1. A blank row or column can cause the region to stop early, leaving records outside the sort. Conversely, unrelated data immediately adjoining the block can be included. Check the selected range before running the macro if the worksheet has notes, totals, or other content nearby.

If your data occupies a known range, you can replace the CurrentRegion line with a fixed address. For example, if headers are in A1:C100 and all 99 records fit within that block, set dataRange to ws.Range("A1:C100"). Update the address as the data grows; a fixed range that ends too early leaves new rows unsorted. Include every column belonging to each record, not only the two keys. Sorting A:B alone when C contains related details can scramble the relationships between rows.

For recurring work, an Excel Table is often easier to maintain because it expands as records are added. You can select a cell in the table and use Data > Sort to set the levels, or write VBA that targets the table’s data range. Whichever method you choose, make sure totals or notes outside the table are not accidentally treated as records.

Change sort order and key columns

The primary and secondary columns do not have to sort in the same direction. For example, to sort Department A–Z and then Start Date newest to oldest, keep Order1:=xlAscending and change the second order to Order2:=xlDescending. The values are xlAscending and xlDescending; use one for each key as needed.

To use other columns, change the key references while keeping them inside the sort range. If the data range starts in A1, column C is the third column, so the second key can be set to dataRange.Cells(2, 3). A typical sequence might be Region ascending, Sales descending, and Product ascending. Add a third key with the Key3 and Order3 arguments if you need that final tie-breaker. The sort priority is the order of the keys, not the order in which columns happen to appear on the sheet.

Text sorts generally follow Excel’s sort rules, while dates and numbers sort by their underlying values when Excel recognizes them as dates or numbers. If a date column contains a mixture of true dates and text that merely looks like dates, the result may not match chronological order. Clean and standardize the key data before sorting when consistency matters.

Common problems and how to prevent them

  • Only two columns move: The sort range is probably too narrow. Include all columns that make up each record so related values stay on the same row.
  • The header is sorted into the records: Confirm that the range begins at the header row and set Header:=xlYes. If the range has no header, use Header:=xlNo instead.
  • Some rows are missing from the result: Check for a blank row or column interrupting CurrentRegion, or use an explicit range that includes all records.
  • The wrong sheet changes: Verify the worksheet name in the code. A macro that names a sheet directly is less ambiguous than one that relies on whichever sheet happens to be active.
  • Dates or numbers appear out of order: Look for numbers stored as text, mixed date formats, leading or trailing spaces, and inconsistent values. The sort cannot reliably group different underlying data types as though they were identical.
  • The sort is not repeatable: When rows tie on both keys, their relative order is not specified by those keys. Add another key, such as a unique ID, if tied records need a defined order.

Also check whether the worksheet is protected and whether filters or hidden rows affect the way you expect the result to appear. A backup is especially useful when the sort range is difficult to verify: sorting is a data-changing operation, and Ctrl+Z will not reverse a VBA macro.

VBA versus sorting from the ribbon

For a one-time sort, the ribbon is usually simpler. Select a cell in the data and choose Data > Sort. In the Sort dialog, choose the primary column under “Sort by,” select the order, and use “Add Level” to specify the second column and its order. Check that the dialog recognizes the header row before confirming. This approach lets you review the criteria without editing code.

VBA is useful when the same sort must be repeated, triggered by another macro, or applied consistently to a known layout. A macro can save time, but it also depends on accurate worksheet names and range boundaries. For a worksheet used by other people, document the assumed layout and provide a clear way to run the macro. For a one-off job or an unfamiliar data set, use the dialog and inspect the selected range first.

Frequently Asked Questions

Can VBA sort by two columns with different directions?
Yes. Set Order1 and Order2 separately. For example, the primary key can use xlAscending while the secondary key uses xlDescending.

Does the macro sort only columns A and B?
No. In the example, A and B are the sort keys, but CurrentRegion is the range that moves. That is important when other columns contain details belonging to the same records.

How do I sort by columns A and C?
Keep the full data range, but use its first and third columns as keys. For the example range starting at A1, the key cells are dataRange.Cells(2, 1) and dataRange.Cells(2, 3).

Can I sort a range that has no header?
Yes. Set Header:=xlNo and make sure the range starts at the first actual record. With no header, Excel treats every row in the range as data.

Can I undo a VBA sort with Ctrl+Z?
No. Excel clears its ordinary undo history when a macro changes the workbook. Save a separate copy before running the sort, or make sure the source data can be restored.

Will this macro run in Excel for the web?
No. Excel for the web does not execute VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro.

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.