VBA Sort Reference Is Not Valid: Causes and Fixes

VBA & macros · Updated November 18, 2026

“The sort reference is not valid” is an Excel sorting error, not a VBA syntax rule. In a macro, it usually means the sort key or the range being sorted is blank, on the wrong worksheet, or outside the data range; make the sort range and key refer to the intended cells on the same sheet.

What the error means

Excel needs two related pieces of information to sort a range: the full block of records to rearrange and the field, or key, that determines their order. For example, if a list occupies A1:D100 and column B contains dates, the sort range is A1:D100 and the sort key is in column B. The key tells Excel how to order the rows; it does not tell Excel which other columns must move with each row.

The message is often displayed as “The sort reference is not valid. Make sure that it’s within the data you want to sort, and the first Sort By box isn’t the same or blank.” The wording can vary by Excel version or by the way the sort is initiated. The underlying issue is usually that Excel cannot reconcile the key with the range it has been asked to sort. VBA may expose the problem when a macro reaches a sort command, even though the macro compiled successfully.

That distinction matters: this is generally a runtime problem, not a spelling or grammar error in VBA. A macro can contain valid VBA statements and still pass Excel an invalid sorting setup. Fixing the cell references and the sort configuration is usually more useful than changing the VBA syntax at random.

How Excel interprets the sort range and key

With the Sort object, SetRange identifies the block that will be reordered, while SortFields.Add identifies a field inside that block. A key that points to another worksheet, or to a column outside the block, is not a sound sort definition. If the records are in A1:D100, sorting by a key in F2:F100 does not include that key in the declared data block.

Headers also affect the row references. If row 1 contains headings, include it in the range being sorted and set Header to xlYes. The key should refer to the data rows under the heading, such as B2:B100. If the range has no heading row, set Header to xlNo and include all records as data. Explicitly choosing the header setting is safer than asking Excel to guess, particularly when a first data value looks like a heading.

The sort range should normally cover every column that belongs to each record. Sorting only A1:A100 when related values are in B1:D100 can separate names from their associated details. Conversely, declaring a broad range that includes unrelated notes or blank columns may pull those cells into the sort. Decide which columns make up one record before setting the range.

Common causes in VBA

  • The key is outside the sort range. The code sorts A1:D100 but uses a key from column E, or the sort range ends before the key column.
  • The key and range use different sheets. An unqualified Range can refer to the active sheet, while the sort range is assigned to a worksheet variable. The references can look plausible but point to different places.
  • A reference is blank or incomplete. A variable used to build the range may not have been set, or a last-row calculation may return a row above the first data row.
  • Header handling does not match the data. A header is treated as a record, or the first record is mistakenly excluded as a header.
  • The macro sorts the wrong block. A recorded macro may retain a fixed address from an earlier workbook layout, even after rows or columns are added.
  • The selected area is discontinuous or irregular. A multi-area selection, inconsistent row lengths, or merged cells within the target can make sorting unsupported or ambiguous. Merged cells are not always the cause of this exact message, but removing or unmerging them is a useful diagnostic when a range otherwise looks correct.

Filters and hidden rows deserve a separate check. A filter does not automatically make every sort invalid, but the macro must be clear about whether it is sorting the whole data block, a table, or a particular range. Avoid relying on the currently selected cells or the active filter state when a repeatable macro can name its target directly.

A dependable VBA pattern

This example sorts a four-column list on a worksheet named Data by its second column, in ascending order. It assumes A1:D100 contains the complete block, row 1 is a header, and rows 2 through 100 are records. Change the worksheet name and addresses to fit the workbook, keeping the key column inside the sort range.

Sub SortRecordsByDate()
    Dim ws As Worksheet
    Dim sortRange As Range
    Dim keyRange As Range

    ' Use explicit worksheet and range references.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sortRange = ws.Range("A1:D100")
    Set keyRange = ws.Range("B2:B100")

    ' Clear any previous sort fields, then define this sort.
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=keyRange, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Because this example declares both ranges from ws, they refer to the same worksheet. The key B2:B100 sits within the data columns A:D and covers the record rows, while the sort range includes the header row. The SortFields.Clear line removes sort fields left on that worksheet’s Sort object before adding the requested field; without a clear step, old sort criteria can remain and affect the result.

Before running any macro, save a copy of the workbook. Excel macros cannot be undone with Ctrl+Z; on a Mac, Command+Z does not reverse a completed VBA sort either. Test on a copy, especially if the data is important or the macro changes several ranges.

Check worksheet and range references

Unqualified references are a frequent source of confusing sorts. For example, Range("A1:D100") and Cells(1, 2) refer to the active sheet unless they are qualified. If a user activates another tab before running the macro, those expressions may target a different sheet from the intended data. Qualify each range through a worksheet variable, as in ws.Range("A1:D100").

Also check which workbook owns the sheet. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel. They are not necessarily the same—for example, code stored in an add-in can act on a different active workbook. Use the object that matches the macro’s intended target, then qualify the worksheet and all its ranges consistently.

For a variable-sized list, inspect how the macro finds its last row. If data starts in row 2, a last-row result of 1 means there are no data rows to sort and a range such as B2:B1 is not a useful key. Handle the empty-data case before constructing the sort. Also consider whether gaps in a column make an “end of region” calculation stop too early; choose a column that is reliably populated or use a clearly defined table boundary.

Tables, headers, and unusual data layouts

Excel Tables, created with Insert > Table, have their own structured boundaries and header row. Sorting a table can be preferable to hard-coded addresses because the table expands as records are added. In VBA, a table is represented by a ListObject; its sort settings can be used to work with the table rather than a guessed cell range. If you use an ordinary worksheet Sort object instead, confirm that SetRange includes the intended table data and that the header option is correct.

Do not assume that a blank row is harmless. A blank row inside a list can split what Excel recognizes as a contiguous region, and a macro based on the current selection may then sort only part of the records. Similarly, totals rows, notes, and separate sections should not be included accidentally. A consistent rectangular list—with one header row and one record per row—is easier to sort reliably than a layout with subtotals or merged headings mixed into the records.

For a multi-level sort, add each key in priority order: the first field is the primary ordering, followed by the next field to break ties. Every key must belong to the block being sorted. If sorting by department and then date, for example, both key columns should be inside the full range containing all the record fields. Review the sort-field order as well as each address when a macro runs without an error but produces an unexpected sequence.

A practical troubleshooting sequence

When the error appears, check the actual references at the moment the macro runs rather than relying only on what the code seems intended to address. The active sheet may have changed, a variable may be empty, or the list may now have a different last row. A focused inspection is quicker than rewriting the entire procedure.

  1. Use the VBA editor’s Debug command when the error appears, so execution stops at the failing statement.
  2. Inspect the worksheet object, the sort range, and each key range. Confirm their sheet names and addresses.
  3. Check that each key column is within the full block, and that the block includes all fields that must move together.
  4. Verify the header setting and make sure the key rows cover the intended records.
  5. Look for empty ranges, multiple areas, merged cells, blank separator rows, or a stale hard-coded address.
  6. Run the macro on a saved copy, then verify both the order and the alignment of values across each row.

On Windows, Alt+F11 opens the Visual Basic Editor in Excel; on Mac, the shortcut may be Option+F11 or Fn+Option+F11 depending on the keyboard and system settings. You can also open the editor from Excel’s Developer tab by choosing Visual Basic. If the error is raised while the macro is running, the highlighted line and the variable values around it are more informative than the final dialog alone.

Preventing the error in future macros

Prefer explicit worksheet variables, a clearly defined data boundary, and an explicit header setting. Avoid building a sort around Selection, ActiveCell, or the currently active sheet unless the user’s selection is deliberately part of the design. Those references make a macro depend on what happened to be selected before it ran.

For recurring workbooks, consider converting the list into a Table and referring to the table by name, or centralize the range-building logic so it is checked before sorting. Validate that the last row is at least the first data row and that the key column exists in the selected block. These simple checks turn a vague sort failure into a clear decision, such as stopping because the list contains no records.

Frequently Asked Questions

Is “The sort reference is not valid” a VBA compile error?
No. It is generally an Excel runtime error: VBA has reached a sorting operation, but Excel cannot use the supplied sort range or key as configured. The code can compile and still fail when executed.

Does the sort key have to be inside the sort range?
Yes, for a normal range sort the key should identify a field within the block being sorted. If the key is outside the block, expand the sort range to include the relevant column or correct the key reference.

Why does my VBA sort work on one sheet but fail on another?
The sheets may have different layouts, or an unqualified Range may point to whichever sheet is active. Qualify the sort range and key with the same worksheet object and confirm that the addresses exist on both sheets.

Can a blank row cause this error?
It can contribute when the macro uses a contiguous-region or last-row method and consequently builds an incomplete or empty range. A blank row does not automatically invalidate every sort, so inspect the actual range the macro produces.

Will Ctrl+Z undo a VBA sort?
No. Running a macro clears Excel’s normal undo history, and Ctrl+Z will not restore the pre-sort row order. Save a copy first or add deliberate backup logic if the macro must preserve the original arrangement.

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.