To sort cells with VBA in Excel, sort the entire data range by a key column—not just one column if the rows contain related records. In a macro, define the range, choose the sort key and order, specify whether the first row is a header, and apply the sort.
What “sort cells” means in Excel
People often say “sort cells” when they mean arranging a list alphabetically or numerically. Excel’s sort operation generally rearranges complete rows within the selected range, so values in each row stay together. If a table has a customer name in column A, a date in column B, and an amount in column C, sorting by the date should move all three values in each record as one unit.
This distinction matters in VBA. Sorting only the name column while leaving the dates and amounts behind can attach the wrong information to each person. Include all columns that belong to the records in the sort range, and use one column in that range as the key. If you truly intend to sort a single independent list, a one-column range is appropriate.
VBA is useful when a sort must be repeated, triggered by a button, or included in a larger process such as importing and cleaning data. For an occasional sort, Excel’s Data tab is usually quicker and lets you inspect the settings before applying them.
Sort a range with VBA
This example sorts rows 2 through the last populated row on a worksheet named Data. The data occupies columns A to C, row 1 contains headers, and column B is the sort key. It sorts the key values in ascending order while keeping each row together.
Option Explicit
Sub SortDataByName()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Do nothing when there are no data rows below the header.
If lastRow < 2 Then Exit Sub
' Sort the complete records in columns A:C by column B.
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:C" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Change the worksheet name, range boundaries, and key column to match your workbook. The last-row calculation looks down column A, so column A should be populated for every data record. If another column is more reliable, use it in the Cells expression. The key range starts at row 2 because row 1 is the header; the full sort range starts at row 1 so the header is recognized.
How the sort settings work
The Sort object makes the sort criteria explicit. SortFields.Clear removes previous criteria held by that worksheet’s sort object, and SortFields.Add adds the key, the type of values to sort, and the order. SetRange identifies all the cells whose rows should move. Apply carries out the operation.
Order:=xlAscendingsorts text from A to Z and numbers from smallest to largest. UsexlDescendingfor Z to A or largest to smallest..Header = xlYestells Excel the first row contains headings. UsexlNowhen every row is data, orxlGuessonly when you want Excel to infer the setting..Orientation = xlTopToBottomsorts rows based on values in a column. Sorting left to right is a different operation and requires a different range layout..MatchCase = Falsemeans the sort does not distinguish uppercase and lowercase text as separate cases.
Use xlSortOnValues for ordinary text, numbers, dates, and cell values. Excel also supports sorting by cell color, font color, or icon through sort-field settings, but those options need additional criteria and are not necessary for a basic value sort.
Sort by more than one column
A multi-level sort is useful when values in the main key repeat. For example, you can sort a roster first by department and then by surname, so people within each department appear alphabetically. Add sort fields in priority order: the first field is the primary key, and later fields break ties.
Option Explicit
Sub SortByDepartmentThenSurname()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
' Sort complete rows: A is Department, B is Surname, C is Employee ID.
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:C" & lastRow)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Here, column A determines the overall order. Column B is considered only among rows with the same department. Add further fields if needed, but make sure each key range is inside the complete range supplied to .SetRange. You can set a different order for a secondary field, such as ascending department and descending date.
Choose the right range and header setting
The most important decision is the sort range. It must cover every column that belongs to the records, as well as the rows to be sorted. A range that stops at column B when the records extend through column D risks leaving columns C and D out of alignment. Before running a macro on unfamiliar data, check the range boundaries against the actual table.
Excel tables can make this easier because their data area expands as rows are added. For a structured table, you can refer to its DataBodyRange and sort its columns, but table-specific code must account for an empty table, where there may be no data body. A fixed range is simpler for a stable layout; a calculated last row is more suitable when records are added over time.
Header detection should be deliberate. If the macro says xlNo when the range includes a heading, the heading can be sorted into the records. If it says xlYes when there is no heading, Excel may leave the first actual record out of the sort. Use xlYes or xlNo based on the worksheet design rather than relying on Excel to guess.
Run the macro safely
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and update the worksheet name and range assumptions. Return to Excel and run it from the Macro dialog, opened with Alt+F8, or connect it to a button if users need to repeat the sort.
On Mac, Excel also supports VBA, though available keyboard shortcuts can depend on the keyboard and system settings. Option+F11 opens the Visual Basic Editor on many Mac keyboards; a Function key may also be required. Excel for the web does not run VBA macros, so open the workbook in the desktop app to execute this code. The exact VBA editor interface and some supported features can differ by platform.
Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, and sorting changes the order of the records. If the workbook needs to retain macros, save it in a macro-enabled format such as .xlsm. Only enable macros in files you trust; a macro can make changes beyond sorting.
Common problems and fixes
- Rows no longer match: The sort range probably omitted one or more related columns. Expand
.SetRangeto include the whole record. - The heading moved: Check that the range includes the header and that
.Headeris set toxlYes. - The macro says a worksheet is missing: The sheet name in
Worksheets("Data")must match the worksheet tab exactly. The code usesThisWorkbook, meaning the workbook containing the macro. - Some rows were not sorted: The calculated last row may be based on a column with blanks. Base the calculation on a consistently populated column, or determine the last row from the actual data structure.
- Numbers sort unexpectedly: Some entries may be stored as text, or the column may contain mixed types. Check the source data and convert values consistently before sorting.
- The sort includes unwanted cells: Blank rows, notes, or unrelated content inside the chosen range can affect the result. Inspect the full range and keep separate data blocks outside it.
Filters and hidden rows deserve attention, too. A normal range sort rearranges cells in the specified range; do not assume it will behave like sorting only the visible filtered records. If a filtered list is involved, test on a copy and confirm that the resulting records and filter state meet the requirement.
VBA sort versus Excel’s built-in sort
For a one-time task, select a cell in the data and use Data > Sort in the Excel ribbon. The dialog lets you choose a sort column, order, and additional levels, and it can help identify whether the range has headers. If Excel asks whether to expand the selection, choose the option that keeps related columns together.
VBA is the better fit when the same sort must run repeatedly, when it is one step in a longer macro, or when you need to standardize the range and criteria for other users. It is less forgiving of layout changes: if a worksheet is renamed or columns are inserted, hard-coded references may need updating. A table-based design or clearly defined range can reduce that maintenance burden.
For a sorted display that updates from source data without rearranging the original cells, a worksheet formula may be more appropriate. The SORT function is available in Microsoft 365 and Excel 2021 or later; it returns a sorted array in another area of the sheet. Unlike a VBA sort, it does not physically reorder the source records. Choose based on whether you want to change the source order or present a separate sorted result.
Frequently Asked Questions
Can VBA sort cells alphabetically?
Yes. Set the sort key to the text column and use Order:=xlAscending for A to Z or Order:=xlDescending for Z to A. Include the other columns in the range if they belong to the same records.
How do I sort without moving the header?
Include the header row in the sort range and set .Header = xlYes. For example, if row 1 is the heading and data begins in row 2, set the full range from row 1 while starting the key range at row 2.
Can I sort only one column?
Yes, when that column is an independent list with no related row data. If each value belongs to a record across other columns, sort the full record range by that column instead so the information remains together.
Why can’t I undo a VBA sort with Ctrl+Z?
Running a macro typically clears Excel’s normal undo history. Make a backup copy before sorting, or keep an untouched source sheet if you need to preserve the original order.
Does this VBA code work in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA macros. Use desktop Excel for Windows or Mac to execute the procedure.
Related
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA UserForm Scrollbar: Setup, Sizing, and FixesTo add a scrollbar to an Excel VBA UserForm, set its ScrollBars property to vertical, horizontal, or both,…
- VBA Find: Check Whether a String Contains a SubstringIn VBA, use InStr to check whether one string contains another: if the function returns a number greater…
- Excel VBA Range Find Exact Match with xlWhole ExplainedTo find a cell whose entire contents match a value in Excel VBA, call Range.Find with LookAt:=xlWhole, and…
- Excel VBA UserForm Examples: Forms, Code, and Setup GuideCommon Excel VBA UserForm examples include a contact-entry form, a search box, a data-editing dialog, and a task-specific…
- Excel VBA Editor: How to Open and Use ItThe Excel VBA Editor, also called the Visual Basic Editor or VBE, is the desktop window where you…
- Excel Macro to Protect a Sheet: VBA Code and SetupTo protect an Excel worksheet with a macro, call the sheet’s VBA Protect method, for example Worksheets("Entry").Protect Password:="change-me".…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.