Using VBA to Sort Contracts in Excel: A Practical Lesson

To sort a contract register by expiration date with VBA, put the records in one rectangular range with headers in row 1, then run a macro that sorts the entire range using the expiration-date column as its key. The example below assumes a worksheet named Contracts, contract IDs in column A, expiration dates in column D, and records in columns A:F; adjust those references to fit your workbook.

What the macro does—and why the range matters

Sorting a contract list is more than putting dates in order: every field belonging to a contract must stay on the same row. If the client name is in column B, the start date is in C, and the status is in F, sorting only column D would detach expiration dates from their contracts. The macro therefore sorts columns A:F as a complete block, using dates in column D to determine each row’s position.

The example sorts expiration dates from earliest to latest, a useful view for identifying contracts that are coming due. Excel recognizes actual date values as dates. A value that merely looks like a date but is stored as text may sort incorrectly, so check the source data before relying on the result.

Prepare a consistent contract register

Use one header row, followed by one contract per row. Avoid blank rows inside the list, merged cells, and separate mini-tables within the same range. In this example, the layout is:

Column A: Contract ID
Column B: Client
Column C: Start date
Column D: Expiration date
Column E: Contract value
Column F: Status

The macro finds the last used row in column A, so every contract row needs a Contract ID in that column. If your register uses another consistently populated column, change the column letter in the last-row statement. Make sure the data range in the code includes every column that should move with each contract. If your register extends through column H, for example, change the range ending in F to one ending in H.

VBA macro to sort by expiration date

This procedure sorts ascending by the dates in column D. In an ascending date sort, older dates appear first. Excel generally places blank cells after dated values in an ascending sort, but it is sensible to verify how blanks are handled in your own register, particularly if your records contain mixed data types.

Option Explicit

Sub SortContractsByExpiration()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range
    Dim sortKey As Range

    ' Use the contract register worksheet.
    Set ws = ThisWorkbook.Worksheets("Contracts")

    ' Column A must contain an ID for every contract row.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No contract rows were found to sort.", vbInformation
        Exit Sub
    End If

    ' Include the header and every field that belongs to each record.
    Set sortRange = ws.Range("A1:F" & lastRow)
    Set sortKey = ws.Range("D2:D" & lastRow)

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=sortKey, SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

The line setting Header to xlYes tells Excel that row 1 contains labels, not a contract record. The sort key starts at D2 for the same reason. The key range and overall sort range must refer to the same worksheet and align with the records being sorted. This code uses standard Excel VBA objects rather than Windows API calls, so it is also suitable for Excel for Microsoft 365 on Mac, subject to the usual difference that the VBA editor and shortcuts may be opened differently.

Add and run the macro in Excel

Save a working copy of the workbook. Choose Excel Macro-Enabled Workbook (*.xlsm) if you need to preserve VBA code.
Open the VBA editor from Developer > Visual Basic. On Windows, Alt+F11 is a shortcut; on Mac, Option+F11 may work, sometimes with the Fn key depending on keyboard settings.
In the editor, choose Insert > Module, then paste the procedure into the module.
Confirm that the worksheet tab is named Contracts and that the column letters and range ending in F match your data.
Return to Excel and run it from Developer > Macros, select SortContractsByExpiration, and choose Run. On Windows, Alt+F8 opens the macro dialog; Mac keyboard shortcuts can vary, so the ribbon command is a dependable route.

If the Developer tab is hidden in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Preferences > Ribbon & Toolbar and enable Developer. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop Excel application for this lesson.

Sort by more than one contract field

A single-key sort is often enough to bring the nearest expirations to the top. A register with many contracts sharing the same expiration date may be easier to scan if ties are sorted by client or contract ID. In the SortFields section, add another key before applying the sort. Excel applies sort fields in priority order, so add the expiration date first and the tie-breaker second. Set the second key to the appropriate range, such as B2:B followed by the final row, and choose ascending or descending order for that field.

For a different business view, change Order:=xlAscending to Order:=xlDescending. Descending dates put the latest expirations first, which can help when reviewing longer-term agreements. The sort order changes the display order only; it does not calculate how many days remain or determine whether a contract is legally active. Those conclusions depend on the date, status, amendments, and the organization’s own records and processes.

Using an Excel Table instead of fixed ranges

If the contract register grows regularly, formatting it as an Excel Table can make it easier to manage. Select a cell in the data and choose Insert > Table, or use Home > Format as Table. Confirm that the table has headers. Tables expand as records are added and provide filter and sort controls in their header row. For a small or occasional task, clicking the filter arrow on Expiration date and choosing a date sort may be simpler than using VBA.

The sample macro uses a fixed A:F range and finds its final row from column A. If you convert the list to a Table, a more maintainable macro can refer to the table by its name and sort its Expiration date column. That approach avoids hard-coding the table’s final row and can reduce the chance of omitting new records. It does require the table name and column header in the code to match the workbook exactly. Do not combine a table-based version with the fixed-range example without revising the range and sort references deliberately.

Common problems and practical checks

Subscript out of range: The workbook does not contain a worksheet named Contracts. Check the sheet tab and update the name in the code; the tab name must match exactly.
Only some fields move: The sort range does not include all contract columns. Extend its last column so each record remains intact.
Some records are missing: The last-row calculation relies on column A. Fill missing IDs or use a different column that is populated on every record.
Dates appear out of sequence: Some entries may be text rather than true Excel dates. Check representative cells and correct the source data before sorting.
Headers get mixed into the list: Confirm the header is in row 1 and that .Header = xlYes is present. If the data begins elsewhere, revise both the range and key references.
The macro is blocked: Use a trusted workbook and follow your organization’s macro policy. Do not enable macros in an unfamiliar file just to make a sort work.

Before running the macro, confirm that filters, hidden rows, and any surrounding notes will not make the result confusing. The code sorts the stated range; it does not inspect whether the workbook has special layouts, formulas referencing row positions, or other processes that depend on the current order. A quick review of the sorted register is part of using the macro responsibly.

Safety, backups, and alternatives

Save a separate copy before running a VBA sort. Macros cannot be undone with Ctrl+Z, so a mistaken range or an unexpected sort may not be reversible through Excel’s normal Undo command. If the output is wrong, close without saving and reopen the backup, or restore a known-good copy. A macro-enabled file may also trigger security warnings; keep macros disabled for files you do not trust and follow workplace security rules.

For a one-time sort, use the header filter or Data > Sort rather than writing code. In the Sort dialog, select the expiration-date column, choose Values, and select Oldest to Newest; confirm that the list has headers and that the entire table is included. A worksheet formula such as SORT can produce a sorted view in supported Excel versions, but it returns a separate result rather than rearranging the original records. VBA is most useful when the same sort must be repeated or attached to a controlled workflow, not simply because a list has dates.

Frequently Asked Questions

Can I sort contracts by expiration date from soonest to latest?
Yes. Use the expiration-date column as the sort key and ascending order. Check that dates are stored as Excel dates and that the full row range is included.

Will this macro work if my dates are in another column?
Yes. Change the column letter in the sortKey range to the date column in your sheet. Keep the key row boundaries aligned with the contract rows.

Can I sort by expiration date and then client name?
Yes. Add a second sort field for the client-name range after the expiration-date field. The date remains the primary order; the client field sorts records with matching dates.

Does VBA run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run this procedure.

Can I undo a macro sort with Ctrl+Z?
No. Excel clears its normal undo history when a VBA macro runs. Save a separate copy first so you can recover the original ordering if needed.

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.