VBA Sort Oldest to Newest: Dates and Macros

VBA & macros · Updated November 18, 2026

To sort dates from oldest to newest with VBA, sort the full data range by its date column in ascending order: set the sort field’s Order to xlAscending, identify whether the range has a header, and apply the sort. The macro below does this for a table in columns A:D, using dates in column A and a filled record ID in column D.

What “oldest to newest” means in Excel

For dates, oldest to newest is an ascending sort: the earliest date appears first and the latest date last. In the VBA object model, the setting that requests this order is xlAscending. The same setting is used for ascending text or number sorts, but the displayed order depends on the values Excel recognizes in the sort column.

Excel stores genuine dates as serial numbers, with time values represented by fractions of a day. Sorting those values in ascending order puts earlier dates first. For example, a date in 2021 comes before a date in 2024; if the cells include times, 9:00 a.m. comes before 3:00 p.m. on the same date. A value that only looks like a date may instead be text, so it might not sort chronologically. Confirm that the cells contain real dates before blaming the sort code.

Run a date sort from Excel’s interface

If you only need to sort once, the ribbon is simpler than a macro. Click a cell in the data, then choose Data > Sort. In the Sort dialog, select the date-column heading in Sort by, choose Cell Values under Sort On, and choose Oldest to Newest under Order. If the first row contains column names, make sure My data has headers is selected, then click OK.

When sorting a range rather than an Excel table, Excel may ask whether to expand the selection. Choose Expand the selection so each row’s other fields move with its date. Sorting only the date column can detach dates from names, amounts, or IDs and corrupt the table’s meaning. An Excel table generally treats a row as a unit, which makes it a convenient structure for regularly updated data.

VBA macro for a four-column list

This example assumes row 1 contains headers, dates are in A, the records occupy A:D, and every data row has a record ID in D. It finds the last used row by looking in D, then sorts all four columns using the dates in A. Change the range and key references if your layout differs.

Sub SortOldestToNewest()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range

    ' Use the active sheet; confirm it is the intended worksheet.
    Set ws = ActiveSheet

    ' Column D must contain an ID on every record row.
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "There are no data rows to sort.", vbInformation
        Exit Sub
    End If

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

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

To add it, open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. On Windows, Alt+F11 opens the editor and Alt+F8 opens the Macro dialog. On Mac, Option+F11 and Option+F8 are common equivalents; some keyboards require holding Fn as well. Ribbon and shortcut behavior can vary by keyboard settings. Save a working copy before running any macro: VBA changes cannot be undone with Ctrl+Z. Save a macro-enabled workbook as .xlsm if you need to retain the code.

How the VBA sort is configured

The macro first clears existing sort fields so that a previous sort rule does not remain in the worksheet’s sort configuration. .SortFields.Add then identifies the key cells, A2 through the last record, and assigns xlAscending. The key excludes the heading because the sort range includes a header row. The .SetRange line is crucial: it tells Excel to move the entire A:D row range, not just the date cells.

.Header = xlYes tells Excel that the first row is a heading, not a record to sort. .Orientation = xlTopToBottom sorts records vertically. Finally, .Apply carries out the configured sort. If your data has no heading, use a range that starts with the first record and set .Header = xlNo; the key range must also start at that first record.

The example uses ActiveSheet for readability, but that means the macro acts on whichever worksheet is active when it runs. For a recurring task, you can replace it with an explicit worksheet reference, such as ThisWorkbook.Worksheets("Log"), using your actual sheet name. This reduces the risk of sorting the wrong sheet.

Adapt the range to your workbook

The most important customization is the boundary of the data. The example determines the last row from column D because its stated assumption is that every record has an ID there. If the ID column contains gaps, or if your data ends in another column, choose a column that is reliably filled for every record and update both the last-row reference and the range. If a record has a blank ID at the bottom, this method may stop early and leave that row out.

  • Dates in another column: change the sort key from column A to the actual date column, and change the key’s row limits to match.
  • More or fewer data columns: adjust sortRange so it includes every field that must stay with each record.
  • A different header row: change the first row in the sort range and key range, and set .Header correctly.
  • An Excel table: a table can be a more resilient choice when rows are added regularly. Its data range expands as records are added, but you still need to identify the correct date field and sort the table’s complete rows.

A worksheet’s CurrentRegion can sometimes identify a contiguous block of data, but blank rows or blank columns can split that block. For that reason, a clearly defined table or a deliberately chosen range is usually easier to reason about than relying on an automatic boundary when the worksheet layout is irregular.

Sort by date, then break ties

Sorting only by date is sufficient when the order of records that share a date does not matter. If multiple rows have the same date and you need a predictable order within each date, add a secondary sort field—for example, sort first by date ascending and then by time or record ID. In the Sort dialog, add another level. In VBA, add another sort field before setting the range and applying the sort, with the date as the primary key and the chosen tie-breaker as the next key.

If cells contain date-and-time values, a date sort will also order different times on the same day because the time is part of the underlying value. If cells contain dates only, records with the same date have no time information to distinguish them. Do not assume Excel will preserve a particular order among equal keys; use a secondary key when that order matters.

Common problems and practical checks

When a result looks wrong, check the source data and the range before rewriting the macro. Mixed types are a frequent cause: some cells may be genuine Excel dates while others are text such as a date imported from another system. Text can sort according to character order rather than calendar order. Formatting a text value to look like a date does not necessarily convert its underlying value. Check a few cells, and use Excel’s date conversion or data-cleaning tools if necessary.

  • Rows no longer match: ensure .SetRange covers all columns in each record. Do not sort just the date column.
  • Some records were not included: verify the last-row anchor column is filled on every record and that the range reaches the actual final row.
  • The heading was sorted into the data: check that the sort range begins at the heading and .Header = xlYes, or that a headerless range uses xlNo.
  • Dates appear out of order: inspect whether values are real dates, text, or a mixture, and check whether date-and-time values include hidden times.
  • The macro will not run: confirm that the workbook is saved in a macro-capable format and that Excel’s security settings permit the macro. Only enable macros from sources you trust.

Blank date cells and invalid entries deserve deliberate handling. A sort may place blanks differently from what a workflow expects, and text entries can produce an order that is technically valid for text but wrong for a timeline. Decide whether incomplete records should remain in the list, be moved to a separate section, or be corrected before sorting. Do not silently discard them by choosing a range that ends at the last nonblank date if other record fields extend farther.

Using the macro safely and repeatedly

Before running the procedure, confirm the active sheet and the stated A:D layout. If the workbook is important, save a separate copy first; VBA operations generally cannot be reversed with the standard Undo command. For a macro used by other people, add checks for the expected worksheet, headings, and date column, and display a clear message if those assumptions are not met. Those safeguards are especially useful when a workbook’s columns may change over time.

You can run the macro from the Macro dialog or assign it to a button. In the Visual Basic Editor, use Debug > Compile VBAProject to catch certain code errors before running, though compilation does not confirm that the selected range or source data is correct. Excel for Mac also supports VBA macros, but its interface and function-key shortcuts can differ from Windows; the sorting logic itself uses the same Excel VBA sort properties.

Frequently Asked Questions

Which VBA setting sorts dates from oldest to newest?
Use Order:=xlAscending in the sort field. Also set the correct key range, full data range, and header option before applying the sort.

Does xlAscending mean oldest to newest?
For genuine Excel dates, yes: ascending order places the earliest date first. The same constant also sorts other values in ascending order, so confirm that the key column contains actual dates if the outcome is unexpected.

Can I sort without a header row?
Yes. Start the sort range and key at the first record and set .Header = xlNo. If the range does have headings, use xlYes so the heading is not treated as a date.

Why does my VBA sort put dates in the wrong order?
Check for text dates mixed with real dates, a date key that points to the wrong column, or a sort range that excludes records. A display format alone does not guarantee that a cell contains an Excel date value.

Can I sort oldest to newest without VBA?
Yes. Select a cell in the list and use Data > Sort, choose the date field, and select Oldest to Newest. Make sure the entire list is sorted together so the fields in each row remain aligned.

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.