In Excel VBA, sort alphabetically by calling Range.Sort with the range to sort, a key cell in the column, Order1:=xlAscending, and the correct header setting. If the column belongs to a table of records, sort the entire data range so names stay matched to their related information.
Sort a standalone column from A to Z
This macro sorts the entries in column A, with a header in A1 and names beginning in A2. It finds the last occupied cell in column A, so you do not have to hard-code the end of the list. Change "Sheet1" to the exact name of your worksheet.
Sub SortNamesAtoZ()
Dim ws As Worksheet
Dim lastRow As Long
' Use the worksheet containing the list.
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Find the last non-empty cell in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Nothing below the header needs sorting.
If lastRow < 2 Then Exit Sub
' Sort the list, treating A1 as its header.
ws.Range("A1:A" & lastRow).Sort _
Key1:=ws.Range("A2"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom, MatchCase:=False
End Sub
Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z in Windows or Command+Z on Mac. This example sorts only column A. Use it only when the values in A are an independent list, not when each cell belongs to a row of related data.
How the VBA sort settings work
Range.Sort is Excel’s built-in sorting method for a range. The range before .Sort says which cells Excel may rearrange. Key1 identifies the sort column, and Order1:=xlAscending requests ascending order. For text, that is generally A to Z; xlDescending reverses the direction. The key cell needs to be in the column being sorted and inside the range being sorted.
The Header argument matters. Use xlYes when the first row of the range contains labels such as Name or Department; Excel leaves that row at the top. Use xlNo when every cell in the range is data. If the setting is wrong, Excel may move a heading into the list or treat the first actual value as a heading.
Orientation:=xlTopToBottom tells Excel to sort down the rows, which is the normal arrangement for a column list. MatchCase:=False means the sort does not distinguish uppercase from lowercase as separate sorting groups. It does not rewrite the capitalization in the cells.
Keep related rows together
If column A contains names and columns B and C contain details such as department and email address, sorting only A can disconnect names from their details. The safe rule is to sort the full rectangular data range and use the name column as the key. For example, with headers in A1:C1 and records below, sort A1:C100 by the values in column A. Excel then moves each row as a unit.
This macro uses the contiguous region around A1. It assumes the headers are in row 1, the sort key is in column A, and the data is a connected block with no entirely blank row or column splitting it from adjacent fields.
Sub SortRecordsByName()
Dim ws As Worksheet
Dim sortRange As Range
' Select the worksheet containing the table.
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Include the contiguous data block that starts at A1.
Set sortRange = ws.Range("A1").CurrentRegion
' Skip sorting if the region has no records beneath its header.
If sortRange.Rows.Count < 2 Then Exit Sub
' Sort every field in each record by the first column.
sortRange.Sort Key1:=ws.Range("A2"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom, MatchCase:=False
End Sub
Inspect the selected region before running the macro, especially if the worksheet has nearby notes or totals. CurrentRegion stops at completely blank rows and columns, but it can include adjacent cells that form one connected block. If the data has a deliberate blank separator or separate blocks, specify the exact range instead, such as ws.Range("A1:C100"), and keep the key cell inside it.
Choose a range and header setting
Use the layout that matches the worksheet rather than assuming every list starts at A1. A headerless list in A1:A50 needs the range A1:A50, a key in A1, and Header:=xlNo. A list with a heading in A1 and values below it needs Header:=xlYes. For records in A1:D200 sorted by the third column, the range should cover A1:D200 and the key should be a cell in column C, such as C2.
A common source of mistakes is using the whole worksheet column as the range when only a small table is meant to move. That can include unrelated cells or create an enormous range to process. Define the precise table or list instead. If the last row is based on the key column, as in the first macro, be aware that rows with a blank key may not be included when they occur after the last populated key. If records can have empty names, find the final row using a column that is always filled, or define the range using another reliable method.
For a simple one-column list, interior blank cells are included in the range if later entries exist. Excel sorts the contents of the specified range; it does not infer that separated sections are separate lists. Check whether blanks should move with the data before sorting, and avoid including footnotes or summary cells in the sort range.
Run and save the macro safely
In Excel for Microsoft 365 on Windows, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code. Press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Return to Excel and use Developer > Macros to select and run it. If the Developer tab is missing, enable it through File > Options > Customize Ribbon.
On Mac, Excel desktop supports VBA, although the editor shortcut can depend on the keyboard and function-key settings: try Option+F11, or Fn+Option+F11. The Developer tab and Visual Basic Editor are also available through Excel’s ribbon and menus. The exact preferences path for showing Developer can vary by Mac release. Excel for the web does not run VBA macros; open the workbook in the desktop application to execute this code.
Macros can change the workbook immediately, and Excel’s ordinary undo history does not reverse a macro. Save a backup or a separate working copy before running one, verify the worksheet name and sort range, and make sure the workbook is from a source you trust before enabling its macros.
Common sorting problems
- The heading gets sorted with the names: set
Header:=xlYeswhen the first row is a heading. If there is no heading, usexlNo. - Names no longer match their details: expand the sort range to include all columns in the records. Sorting just the key column is appropriate only for a genuinely standalone list.
- The wrong worksheet is changed: confirm that the worksheet name in
Worksheets("Sheet1")is correct.ThisWorkbookmeans the workbook containing the macro, which may differ from the workbook currently active in Excel. - The list is cut off: check for blank key cells at the bottom and confirm the last-row calculation uses a column populated through every record.
- Excel says macros are disabled: use the desktop app and follow your organization’s security rules. Do not enable macros from an untrusted file simply to make a sort run.
Alphabetical order is not always the same as a person’s preferred “natural” order. For instance, text labels such as Item 10 and Item 2 may not sort in the numeric sequence a reader expects. Cells containing actual numbers, text that looks like numbers, punctuation, and accented characters can also produce results that differ from a simple dictionary-style expectation. If the result matters, test a copy with representative values and inspect the output.
VBA versus sorting from the ribbon
For a one-time sort, the ribbon is usually quicker and makes it easy to review Excel’s choice about expanding the selection. Select a cell in the data, then choose Data > Sort A to Z for an ascending sort. If the selection is just one column of a wider table, Excel may ask whether to expand the selection. Choose the option that keeps related row values together. The Sort dialog is useful when you need to choose a specific key, order, or header treatment.
VBA is useful when the same sort must be repeated, when a button should run it, or when it is part of a longer macro. A recorded macro can show the broad shape of Excel’s sorting code, but recorded ranges and worksheet references may be fixed to the original selection. Review and adjust them before relying on the code in a workbook with changing data.
For a sorted view that updates as source data changes, a worksheet formula may be a better fit than a macro. Excel’s SORT function is available in Microsoft 365 and Excel 2021 or later, but it returns a separate sorted result rather than rearranging the source cells. Use VBA or the ribbon when the original records themselves need to be reordered.
Frequently Asked Questions
How do I sort a column alphabetically in VBA?
Set the target range and call its Sort method with a key cell in the column, Order1:=xlAscending, and the appropriate Header setting. Sort the whole record range if other columns belong to the same rows.
Should I use xlYes or xlNo for Header?
Use xlYes if the first row of the range contains column headings. Use xlNo if the first cell in the range is a value that should be sorted.
How can I sort from Z to A instead?
Change Order1:=xlAscending to Order1:=xlDescending. Keep the range, key, and header setting appropriate for your data.
Can I sort a column without moving the other columns?
Yes, if it is an independent list and the neighboring cells are not related to its entries. If each row is a record, sort all the record columns together to prevent mismatches.
Can Excel for the web run this VBA macro?
No. Excel for the web can open many macro-enabled workbooks but cannot run VBA. Use Excel for Windows or Mac desktop to execute the macro.
Check the result after sorting
After the macro finishes, confirm that the header remains at the top, the first and last expected entries are present, and each record’s fields still line up. If the sort is part of a recurring workflow, test it with a small copy of the real layout, including blank cells and unusual names. Clear range boundaries and an accurate header setting are more important than making the macro longer: they determine what Excel is allowed to rearrange.
Related
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Outlook Excel VBA: Automate Email and Calendar TasksYes—Excel VBA can automate classic Outlook for Windows, including creating email drafts from spreadsheet rows and working with…
- Copy Paste Values in Excel VBA: Methods and ExamplesTo copy a range’s results—not its formulas—in Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteValues. For a quick…
- Excel VBA: How to Copy a UserForm to Another WorkbookTo copy an Excel VBA UserForm to another workbook, export the form from the source workbook’s VBA project…
- Excel VBA Array of Worksheets: Create and Use OneIn Excel VBA, you can work with several worksheets as a group by passing an array of sheet…
- VBA Delete Blank Rows in Excel: Safe MethodsYes. In Excel for Microsoft 365 on Windows, a VBA macro can scan a chosen range from bottom…
- Excel VBA MsgBox Carriage Return: Line Breaks ExplainedTo put a carriage return and line break in an Excel VBA MsgBox, join the message parts with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.