To sort data with Excel VBA, set the range to sort, add one or more sort keys, specify whether the first row contains headers, and apply the sort. The example below sorts columns A:D by the values in column B, in ascending order, in desktop Excel for Microsoft 365 on Windows.
What the basic sort macro does
This macro sorts complete rows together: it does not rearrange just the values in column B. It assumes row 1 contains column headings, records begin in row 2, column A is filled for every record, and the data occupies columns A through D. Change the sheet name, range, or sort-key column to match your workbook. Before running any macro, save a copy of the workbook; VBA changes cannot be reversed with Ctrl+Z.
Option Explicit
Sub SortByColumnBAscending()
Dim ws As Worksheet
Dim lastRow As Long
Dim sortRange As Range
' Set the worksheet and find the last record using populated column A.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No data rows to sort.", vbInformation
Exit Sub
End If
' Include every column that belongs to each record.
Set sortRange = ws.Range("A1:D" & lastRow)
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
The sort key starts at B2 because row 1 is a heading. The full sort range starts at A1 so the headings are included in the operation, while .Header = xlYes tells Excel not to treat them as records. With a range like A1:D10, the macro sorts the nine data rows and keeps the values in each row together.
Set up the workbook and run the macro
In Windows Excel, save the file as an Excel Macro-Enabled Workbook (.xlsm) if it contains macros you need to keep. Open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. Change Sheet1 to the worksheet tab name, exactly as it appears, and adjust the range columns if your records span more or fewer than A:D.
- Return to Excel and save a backup copy before running the sort.
- Use Alt+F8, select
SortByColumnBAscending, and choose Run. You can also run it from the Visual Basic Editor. - Check several rows after the sort to confirm the values in all columns still belong to the correct record.
If the Developer tab is hidden, enable it through File > Options > Customize Ribbon, then select Developer. Excel for Mac also supports VBA, though keyboard shortcuts and some ribbon details differ by keyboard and version; use the Mac Visual Basic Editor and macro commands available in that installation. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Run this code in the desktop app.
Sort by multiple columns
For a multi-level sort, add sort fields in priority order. Excel applies the first key first, then uses the next key to order rows tied on the first key. For example, this version sorts by department in column A alphabetically, then by date in column B from newest to oldest. It assumes row 1 has headers and that records span A:D, with column A populated for every record.
Option Explicit
Sub SortByDepartmentAndDate()
Dim ws As Worksheet
Dim lastRow As Long
Dim sortRange As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set sortRange = ws.Range("A1:D" & lastRow)
With ws.Sort
.SortFields.Clear
' Primary key: department, A to Z.
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
' Secondary key: date, newest to oldest within each department.
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
For three levels, add a third .SortFields.Add line before .SetRange. The key range for each level should be inside the overall sort range and cover the data rows, not the heading row. The order in which fields are added matters: put the most important grouping or ordering rule first.
Choose the correct range and header setting
A sort can give misleading results if its range leaves out a column that belongs to the records. For instance, sorting A:B when a person’s address is in C can separate addresses from names. Set the sort range to include every field that must move together, including any relevant columns that are not themselves sort keys. Do not include unrelated notes or a second table below the data.
The example finds the final row by looking upward in column A. This is efficient for a continuous list with an always-populated ID or other field in that column. If column A can be blank on the last record, Excel may stop early and leave records unsorted. Use a column that is reliably populated, or determine the last row using another method appropriate to the sheet. If there are blank rows inside the records, first decide whether they are intentional separators; a simple last-row check does not make separated blocks into one clean table.
Set .Header = xlNo when the selected range has no header row. In that case, include the first data row in the sort key as well as the overall range. Setting the header option incorrectly can either sort a heading among the records or leave a data row out of the sort.
Ascending, descending, text, and dates
Use xlAscending for ascending order and xlDescending for descending order. For text, ascending generally places values alphabetically; for numeric values and real Excel dates, it orders by their underlying values. A date that is stored as text may not sort chronologically as expected. If dates appear out of sequence, check that the cells contain actual date values and are consistently interpreted, rather than merely formatted to look like dates.
Sorting also depends on the kind of data in the key column. A column mixing numbers stored as text with numeric values can produce an order that surprises readers. Blank cells, inconsistent spaces, and inconsistent spelling can affect how a list groups or appears. Clean the source values when possible, and inspect a small test copy if the data has mixed types or special requirements. The DataOption:=xlSortNormal argument in the examples uses Excel’s normal value sorting; it does not convert inconsistent entries into a consistent data type.
Common problems and safer habits
- Wrong worksheet name:
Worksheets("Sheet1")must match the worksheet tab name. A missing or misspelled name causes a run-time error. - Only one column moves: The sort range is too narrow. Expand
sortRangeto cover the complete records. - The heading is sorted as data: Check that the first row really contains headers and that
.Headeris set toxlYes. - Some rows do not move: The last-row calculation may use a column with blanks, or the chosen sort range may stop before the full list.
- The macro cannot be undone: Excel’s Undo history does not restore VBA macro changes. Save a separate copy before running code that changes data.
Use macros only from a workbook and source you trust. Excel’s macro security settings are intended to reduce the risk of running unsafe code; do not enable macros merely because a workbook prompts you to. Keep a recoverable copy of important data and, for a high-impact workbook, test the procedure on a duplicate before using it on the working file.
When to use VBA instead of a manual sort
For a one-time sort, the Data tab’s Sort command is often quicker and makes the selected columns and sort levels visible. A VBA macro is useful when the same operation must be repeated, triggered as part of a larger procedure, or applied consistently to a known range. It can also reduce accidental differences between repeated manual sorts, provided the assumptions in the code remain true.
Excel tables are another practical option for ongoing lists. Their headers and data boundaries are managed as a table, and the filter controls in the header allow interactive sorting. A table does not remove the need for care in a macro, but it can make a growing dataset easier to maintain than a fixed range such as A1:D100. Choose the simplest method that fits the task: manual sorting for occasional work, table controls for interactive lists, and VBA for repeatable desktop automation.
Frequently Asked Questions
How do I sort an Excel range in VBA without a header?
Set .Header = xlNo, and make the sort-key range begin with the first data row. Ensure the overall range includes all columns in each record so rows remain intact.
How do I sort in descending order?
Change the relevant field’s Order:=xlAscending argument to Order:=xlDescending. For a multi-key sort, set the direction separately for each key.
Can I sort by two columns in VBA?
Yes. Add two sort fields before setting and applying the overall range. The first field is the primary sort, and the second orders records that tie on the first.
Can I run VBA sorting in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to use the macro.
Why does my macro leave some rows unsorted?
Check that the overall range includes all records and their related columns. Also check whether the column used to find the last row is blank near the bottom, and whether blank rows divide the data into separate blocks.
Related
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Copy and Paste Values OnlyTo copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues.…
- VBA Select, Copy and Paste in Excel: Practical GuideIn Excel VBA, you can select a range, copy it, and paste it elsewhere, but most macros work…
- VBA MsgBox If: Check a User’s Choice with an If StatementIn VBA, use MsgBox inside an If statement to show a prompt and run different code according to…
- Excel VBA Current Worksheet: ActiveSheet ExplainedIn Excel VBA, the current worksheet is usually the sheet the user is looking at, and VBA refers…
- VBA Range GoTo: Jump to a Cell or Range in ExcelTo jump to a cell or range with VBA, use Excel’s Application.Goto method, for example: Application.Goto Reference:=Worksheets("Sheet1").Range("B5"), Scroll:=True.…
- VBA Excel Dictionary Example: Code and SetupA VBA Dictionary stores values under unique keys, making it useful for fast lookups and totals by name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.