To sort Excel rows with VBA, define the complete data range, choose a key column, and apply a top-to-bottom sort. The macro below sorts A1:D100 by column B in descending order while keeping each row’s values together.
How VBA sorting works
A row sort reorders records vertically according to the values in one or more columns. For example, a sales table with customer names, dates, amounts, and regions should move each complete record as a unit when it is sorted by amount. Sorting only the amount cells would separate amounts from their customers and corrupt the data.
Excel’s VBA Sort object lets you specify the full range to move, the key range to compare, the order, and whether the first row contains headings. The key must be inside the range being sorted. For ordinary lists, xlTopToBottom is the orientation that sorts records down the worksheet; sorting left to right is a different operation.
A basic macro to sort rows
This example assumes a worksheet named Data, headings in row 1, and data in columns A through D from rows 2 to 100. It sorts the records by column B, largest value first. Change the worksheet name, range, or order to match your workbook.
Sub SortDataBySales()
Dim ws As Worksheet
Dim sortRange As Range
Dim keyRange As Range
' Set the worksheet and the complete block of records.
Set ws = ThisWorkbook.Worksheets("Data")
Set sortRange = ws.Range("A1:D100")
Set keyRange = ws.Range("B2:B100")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=keyRange, SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
SortFields.Clear removes sort levels left over from a previous operation on that worksheet’s sort settings. SetRange identifies all columns that must travel together. The key starts at row 2 because row 1 is a header, while .Header = xlYes tells Excel not to sort that header as a record. Use xlAscending for A-to-Z or smallest-to-largest order.
Use a dynamic range when the row count changes
A fixed end row is simple but can omit new records or include unused rows. For a list that grows, determine the last used row in a dependable column, then build both the sort range and key range from that number. This version assumes column A contains a value on every record and that the table spans columns A through D.
Sub SortChangingList()
Dim ws As Worksheet
Dim lastRow As Long
Dim sortRange As Range
Dim keyRange As Range
' Find the final populated row in the record identifier column.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Do nothing if there are no data rows beneath the heading.
If lastRow < 2 Then Exit Sub
Set sortRange = ws.Range("A1:D" & lastRow)
Set keyRange = ws.Range("B2:B" & lastRow)
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=keyRange, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
The last-row method uses Excel’s equivalent of moving upward from the bottom of column A to its last nonblank cell. Choose a column that is reliably filled for every record, such as an ID. If column A can be blank on the final records, this method may stop too early; find the end using a more reliable column or adjust the method to suit the layout. If the sheet contains only a heading, the check prevents an invalid attempt to sort a header by itself.
Sort by multiple columns
For a primary and secondary order, add sort fields in priority order. For example, sort first by region and then by sales amount. Excel sorts records by the first key; when several records tie on that key, it uses the next key to order those tied records. Add both fields before setting the range and applying the sort:
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("D2:D100"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("B2:B100"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
.SetRange ws.Range("A1:D100")
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Here, column D is the primary key and column B is the secondary key. If the intended priority is amount first and region second, reverse the two .SortFields.Add calls. Each key range must line up with the same data rows in the overall sort range.
Headers, blank cells, and choosing the right range
The range is as important as the sort key. Include every column that belongs to each record, including notes or identifiers that may not look relevant to the ordering. If the list occupies A:D, setting the range to only B:B sorts the key cells without moving the corresponding values in A, C, and D.
- Header present: Include the header row in the full sort range and set
.Header = xlYes. Keep the key range limited to data rows. - No header: Start the full range and key range at the first record, and set
.Header = xlNo. - More columns added: Expand the full range so newly relevant fields move with their records.
- Blank cells in the key: Excel places blanks according to its sort behavior and order. If blank values need special treatment, consider adding a helper column that explicitly assigns them a sort rank.
Do not rely on guessing whether Excel will detect a header correctly. Setting the header option explicitly makes the macro’s intent clear. Also check for a blank row dividing the records: a sort range that stops before it will not include records below it.
Sorting Excel tables and filtered lists
Excel tables, created with Insert > Table, are often a good choice for changing datasets because they have named columns and expand as records are added. A table’s header is part of its structure, so avoid treating the header as an ordinary data row. For a recurring process, refer to the table by its name and the key by its column name rather than hard-coding the last row; this makes the intent easier to maintain if the table changes size.
Filtered lists need care as well. Apply the sort to the complete list or table, not to a manually selected subset of visible cells. Sorting a partial selection can mismatch fields or produce a warning that the adjacent data will not be included. If a filter is active, confirm that the intended records are in the sort range and verify the result after the macro runs. Sorting and filtering are separate operations: a sort changes order, while a filter hides records that do not meet its criteria.
Common VBA sort problems and fixes
- “Subscript out of range”: The worksheet name in the code does not exactly match a sheet in the workbook referenced by
ThisWorkbook. Check spelling and spaces, or change the reference to the workbook that actually contains the data. - Only one column moves: The sort range is too narrow. Expand
.SetRangeto include every field in each record. - The heading moves into the data: Confirm that the full range includes the heading and that
.Headeris set toxlYes. If the list has no heading, usexlNo. - New records are not sorted: A fixed range or an unreliable last-row column is missing them. Use a dynamic range or a table reference.
- The order looks wrong: Check whether the key values are stored as numbers or dates rather than text. Values that look numeric but are text may sort differently from true numbers; convert or normalize them before sorting.
- A sort fails after changing code: Clear old sort fields, confirm each key is within the sort range, and check that the range contains more than one row of data.
Mixed data types can be especially confusing. For example, text values such as “100” and “20” do not necessarily sort like the numeric values 100 and 20. A macro cannot infer the intended meaning from appearance alone, so correct the underlying data type or use a helper field with consistent values.
Run macros safely in Excel
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste the macro into the module. Return to Excel and run it from the editor or through Developer > Macros. If the Developer tab is not visible, enable it in File > Options > Customize Ribbon. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).
Excel for Mac supports VBA, but its interface and keyboard behavior differ. The editor shortcut is commonly Option+F11; on some keyboards, you may also need Fn. The Developer tab or Macros dialog can be used if the shortcut does not open the editor. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for this code.
Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, so a mistaken sort may require restoring the saved copy or using another backup. Run macros only from workbooks you trust, and review the range and worksheet references before execution.
When to use VBA instead of the Sort command
For a one-time sort, Excel’s built-in command is usually quicker: select a cell in the list, then use Data > Sort to choose a column and order. It is easier to inspect the settings before applying them, and you do not need to enable macros. The Sort dialog also supports multiple levels through Add Level.
VBA is useful when the same sort must happen repeatedly, when a button or another macro should trigger it, or when a workflow needs a consistent sort range and order. A worksheet formula such as SORT can display sorted results in a separate spill range in supported versions, but it does not rearrange the original records. Choose between a formula, the Sort command, and VBA based on whether you want a live sorted view, a one-off rearrangement, or a repeatable automated action.
Frequently Asked Questions
How do I sort rows by one column in Excel VBA?
Set the complete range in .SetRange, add the desired key with .SortFields.Add, set the header and orientation, and call .Apply. Use the full record range so all related columns move together.
How do I sort in descending order?
Set the sort field’s Order argument to xlDescending. Use xlAscending for ascending order. The same choice applies whether the values are numbers, dates, or text, though mixed or text-formatted values may not behave as expected.
Can I sort rows by two columns with VBA?
Yes. Add one sort field for each key before applying the sort. The first field is the primary order; later fields break ties in earlier fields. Make sure each key covers the data rows and belongs to the overall range.
Why does my sort move the header row?
The header setting may be incorrect or the header may not be included in the range as expected. For a range with headings, include the header in the full range and set .Header = xlYes. For a list without headings, use xlNo.
Can I run VBA sorting in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; the built-in sort commands remain available in the web version.
Related
- QuickBooks Excel and CSV Imports: What You Can Bring In
- Python Excel: Remove Duplicates with pandas or openpyxl
- How to Protect and Unprotect Sheets in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Editor: How to Open and Use the VBA EditorThe Excel macro editor is the Visual Basic Editor (VBE), the built-in environment for viewing, writing, and debugging…
- VBA Reset AutoFilter: Clear Filters, Keep the SetupTo reset an Excel AutoFilter in VBA while keeping its dropdown arrows, clear the active filter rather than…
- Excel VBA: Open a File Dialog to Select a FolderTo let someone choose a folder in Excel VBA, use Application.FileDialog(msoFileDialogFolderPicker). Show the dialog, check whether the user…
- Excel VBA Range Syntax: Examples and Best PracticesIn Excel VBA, a range is a cell or group of cells, usually referenced with Range("A1") or Range("A1:C5").…
- Excel Macro Button: How to Add, Assign, and Run MacrosAn Excel macro button is a clickable control that runs a VBA macro in a workbook. In Excel…
- Excel VBA Pivot Charts: How to Create and Refresh ThemYes—VBA can refresh, filter, and manage Excel PivotCharts, and you can create one by selecting a PivotTable and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.