Excel VBA Sort A to Z: Code, Options, and Common Fixes

To sort a list A to Z with Excel VBA, sort the entire data range by its chosen column and set the order to xlAscending. The macro below sorts columns A through D on a worksheet named Data by the values in column B, keeps the header row in place, and finds the last row automatically.

What an A-to-Z sort does

In Excel, A to Z means ascending order. For text, that generally places entries beginning with earlier letters before entries beginning with later letters; Z to A reverses that order. The same ascending setting sorts numbers from smallest to largest and dates from earliest to latest. Excel sorts according to the underlying cell values and data types, so a column that looks like text may not behave like a column of true numbers or dates.

A VBA sort needs two important pieces of information: the range of records to move and the column that determines their order. If a record has a name, department, and ID in separate columns, sorting only the name cells would detach names from their other details. Set the full table or list as the sort range, then identify the name column as the sort key. A header row should also be identified explicitly so Excel does not move the headings into the data.

The example uses column B as the key and assumes row 1 contains headings. Change those references to fit the layout of your workbook before running it.

Ready-to-use VBA code

This macro finds the last used row by looking down column A, then sorts the records in A:D by column B. It expects a worksheet named Data, headings in row 1, and records starting in row 2. Column A needs a value in each record so it can reliably identify the end of the list.

Sub SortDataByNameAtoZ()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the named worksheet rather than whichever sheet is active.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last record by checking the anchor column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if the sheet has headings but no records.
    If lastRow < 2 Then
        MsgBox "There are no data rows to sort."
        Exit Sub
    End If

    ' Sort all four columns together, using column B as the key.
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:D" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

The sort key is B2 through the last data row, while the sort range includes A1:D and therefore includes the headings and every field belonging to each record. xlAscending requests A to Z order. xlYes tells Excel that the first row is a header; xlNo would be appropriate only if the range had no headings. MatchCase = False avoids separating uppercase and lowercase text into case-sensitive groups.

Adapting the range and sort column

Adjust the worksheet name, key column, range width, and header-row assumption together. For example, if your list occupies B1:F250 and the names are in column D, the sort range must cover B:F and the key must cover the data cells in D. Do not change just the key if the records extend beyond column D; otherwise, values in the omitted columns will not move with their records.

Different worksheet: Replace Data with the exact worksheet tab name. The name is case-insensitive, but spaces and punctuation must match.
Different key: Replace the B2:B reference with the key column and begin below the header row.
More or fewer fields: Extend or shorten the A1:D range to include all columns that belong to the records.
Another header row: If headings are on row 3, for example, set the range to begin at row 3 and the key range to begin at row 4.

The macro uses column A to find the last row, not the sort-key column. That choice is useful when some names in column B are blank, provided column A is filled for every record. If column A can also be blank, choose another consistently populated column as the anchor. A worksheet with gaps or separate lists may need a more specific range rather than a single last-row calculation.

Run the macro in Excel for Windows

Save a copy of the workbook before running any macro. VBA changes cannot be reversed with Ctrl+Z, and a mistake in the selected range can rearrange records in a way that is difficult to reconstruct.

Open the workbook in the desktop version of Excel and save it as an Excel Macro-Enabled Workbook (.xlsm) if it is not already in that format.
Open the Visual Basic Editor with Alt+F11. You can also use Developer > Visual Basic if the Developer tab is visible.
In the editor, choose Insert > Module, then paste the macro into the new module.
Change the sheet name or range assumptions if needed. Confirm that the worksheet contains the expected headings and columns.
Return to Excel and run the macro with Alt+F8, select SortDataByNameAtoZ, and choose Run.

If the Developer tab is missing in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. Excel for Mac has a Visual Basic Editor and supports many VBA macros, but keyboard shortcuts can depend on the keyboard and function-key settings; use the Developer ribbon or the Tools > Macro menu when a shortcut differs. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Open the file in desktop Excel to use this code.

Sorting a table or filtered list

If the data is an Excel Table, sorting its full table range is often safer than guessing a rectangular address. Tables expand when rows are added and keep fields grouped as records. You can still use VBA, but make sure the sort range covers the table rather than only its visible name column. A table’s built-in filter arrows also provide a quick A-to-Z sort without a macro, which can be preferable when a person is sorting manually rather than automating a repeated task.

For a filtered list, sort the complete data range or table, not a manually selected subset of visible cells. Excel’s normal sort works with filtered lists and preserves each record’s fields together when the full range is sorted. Hidden or filtered-out rows can make the result seem surprising because not every row is visible while the operation takes place. Check the filter state and inspect the full list afterward if the macro is part of a reporting process.

Be careful with CurrentRegion if you replace the fixed A:D range with a dynamically detected region. It identifies a block of cells bounded by blank rows or columns, so an empty column inside a dataset can split the region. Conversely, a note or unrelated value directly beside the list may be included. Explicitly defining the data range or using an Excel Table makes the intended boundaries clearer.

How Excel orders text and mixed values

A to Z is not always the same as sorting characters by a simple universal alphabet. Excel applies language and regional sorting rules, which can affect accented letters and other characters. With MatchCase = False, uppercase and lowercase versions are normally treated without a separate case-sensitive order. If case-sensitive sorting is genuinely required, set MatchCase to True and verify the result for the workbook’s language and data.

Numbers stored as text are a frequent source of confusion. A text sequence such as 1, 10, 2 may sort alphabetically rather than numerically, while actual numeric values sort by magnitude. Likewise, dates imported as text may not sort chronologically. Convert inconsistent values to a consistent type before sorting if the intended order is numeric or chronological. Blank cells and duplicate names are allowed, but an A-to-Z sort does not make duplicate records unique. Do not assume that rows with identical key values will retain a particular relative order; add a secondary sort key if their order matters.

Common problems and fixes

“Subscript out of range” appears: Excel could not find a sheet named Data in the workbook containing the code. Check the exact tab name and confirm that the intended sheet is in ThisWorkbook.
Nothing sorts, or only part of the list moves: Verify the last-row anchor and expand the sort range to include every field. If records are not contiguous, use a deliberate range or a Table rather than relying on a single column’s last cell.
The heading is sorted as if it were data: Check that row 1 really contains the header and that .Header = xlYes matches the range. If the range has no header, use xlNo.
Rows become mismatched: The sort range probably covered only one column or omitted fields. Sort the entire record range, not just the key cells.
Numbers or dates look out of order: Check whether the cells contain real numeric/date values or text that only looks like one. Normalize the data types and sort again.
The macro is blocked or unavailable: Confirm that the workbook is opened in desktop Excel, saved in a macro-enabled format, and that macros are permitted by your organization’s security policy.

When a sort gives an unexpected result, first undo nothing and avoid rerunning the macro repeatedly. Compare the current data with the saved copy, inspect the range and key, then correct the macro. Because VBA operations do not populate Excel’s normal undo history, a backup is the dependable recovery option.

When to use VBA instead of a manual sort

Use VBA when the same sort needs to happen repeatedly, when it is one step in a larger workbook process, or when a button or scheduled workflow should apply a consistent range and key. A manual sort is simpler for a one-time task: select a cell in the relevant table column and use the sort command on the Data tab, taking care to expand the selection if Excel asks. A manual sort also makes it easier to inspect the chosen column before making a change.

For a formula-driven sorted view that leaves the source list untouched, Microsoft 365 and Excel 2021 include the SORT function. With a source range A2:D100 and the desired sort key in its second column, =SORT(A2:D100,2,1) returns the records in ascending order by that column in supported versions. It spills results into neighboring cells, so the output area must be clear; it does not replace a macro when you need to rearrange the original records or automate other actions. Excel for the web supports many modern worksheet functions, but it still does not run VBA.

Frequently Asked Questions

What is the VBA code for sorting a column A to Z?

Use an ascending sort order, such as Order:=xlAscending with the SortFields method. Set the full record range separately from the key column so the other fields stay with each sorted row.

How do I sort A to Z but keep the header row?

Include the header in the sort range and set .Header = xlYes. If your sort range starts below the headings, set the header option to xlNo because that range has no header row.

Can I sort a range without a header?

Yes. Set .Header = xlNo and make the range begin with the first data row. Otherwise, Excel may treat the first record as a heading and leave it out of the sort.

Can an Excel macro sort automatically when I add a row?

It can be set up to run in response to a worksheet event, but that requires extra event code and care to avoid repeated triggers or sorting while someone is editing. For most workbooks, run a dedicated macro or use a Table’s manual sort unless automatic sorting is a clear requirement.

Can I run VBA sorting in Excel for the web?

No. Excel for the web can display and edit many workbook contents, but VBA macros must be run in desktop Excel for Windows or Mac. You can use the web app’s regular sorting tools for a manual sort.

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.