VBA does not have a standalone Sort function for worksheet data; in Excel macros, sorting is usually done with the worksheet’s Sort object or a range’s Sort method. For a table with a header row, the SortFields approach is the clearest way to sort a complete range by one or more columns.
Is There a VBA Sort Function?
Not in the same sense as a built-in function that returns a sorted array. Excel VBA sorts worksheet cells through object methods. The two common choices are the Sort object, which lets you set up one or several sort fields, and Range.Sort, a compact method for straightforward sorts.
This distinction also helps avoid confusion with Excel’s worksheet SORT function. A worksheet function returns sorted data in cells; VBA’s sorting methods rearrange cells in a worksheet range. The worksheet function is available in Microsoft 365 and Excel 2021 or later, while the VBA examples here use Excel’s object model and do not depend on that function.
For example, if a worksheet has names in column A, sales figures in column B, and regions in column C, a VBA sort can rearrange whole rows by the sales figures. It is important to sort the whole table, not just column B, so each name and region stays with its original sales value.
A Recommended SortFields Example
This macro sorts a table on a worksheet named Data. It assumes the headers are in row 1, records occupy rows 2 through 20, and sales values are in column B. The entire A:C range is sorted by sales, largest to smallest.
Sub SortBySales()
Dim ws As Worksheet
Dim sortRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set sortRange = ws.Range("A1:C20")
' Sort the complete table by the sales column.
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B20"), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Change the worksheet name, range, and key range to match the workbook. The key range contains the data cells in the column used for ordering; the sort range contains every column that must move together. The xlDescending setting puts larger numeric values first. Use xlAscending for smaller-to-larger numbers or A-to-Z text order.
Save a copy of the workbook before running a macro that changes data. Excel macros cannot be undone with Ctrl+Z, so a backup is the simplest way to restore the original order if the range or settings are wrong.
How the Sort Object Works
The code configures a sort in stages. First, .SortFields.Clear removes sort rules left over from an earlier sort on that worksheet. Next, .SortFields.Add defines the column to use and the direction. .SetRange identifies the full block of cells that should be rearranged. Finally, the header and orientation settings are applied, and .Apply performs the sort.
The order matters conceptually: a key tells Excel how to compare rows, while the set range tells it which rows and columns to move. If the key is column B but the set range contains only column B, Excel will sort the values without moving the related names or regions. That may break the relationships in the table.
.Header = xlYes tells Excel that the first row in the set range contains labels, not records. If there is no header row, use xlNo and include the first data row in the key range. The xlGuess option asks Excel to infer whether a header exists, but explicitly setting xlYes or xlNo is generally more predictable in a macro.
Sorting by Multiple Columns
To sort by more than one field, add multiple sort fields before applying the sort. The first field is the primary order; later fields break ties. For example, sort by region A-to-Z first, then by sales largest-to-smallest within each region. Add a second call to .SortFields.Add before .SetRange:
.SortFields.Clear
.SortFields.Add Key:=ws.Range("C2:C20"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("B2:B20"), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:C20")
.Header = xlYes
.Apply
Here, column C is the primary key and column B is the secondary key. If two rows have different regions, sales does not determine their relative position across regions; sales only orders rows that share a region. To change the priority, add the fields in the order that matches the intended hierarchy.
A Shorter Range.Sort Method
For a simple, single-key sort, Range.Sort can be more concise. With the same table in A1:C20 and sales in column B, this statement sorts the table by sales in ascending order:
ws.Range("A1:C20").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlYes
In this form, Key1 identifies the key column, and Order1 sets its direction. The method supports additional keys and options, but a long list of arguments can be harder to read and maintain. Prefer the SortFields object when you need several keys, want to make each setting explicit, or expect to adjust the sort later.
Use either method consistently for a given task. Avoid mixing an old worksheet sort configuration with a new one unless you deliberately clear or replace the fields. For recurring macros, the explicit setup in the first example makes the intended range, header handling, and sort order easier to inspect.
Headers, Tables, and Changing Data Sizes
Fixed ranges such as A1:C20 are easy to understand, but they stop at row 20. If users add records, the macro must calculate the last row or use an Excel table. A common approach is to find the last used cell in a specific key column, then build a range that begins at the header. This is appropriate only if that column is reliably filled for every record; otherwise, the detected last row may be too early.
Excel tables, created with Insert > Table, expand as records are added and have named columns. A table’s sort settings can be addressed through its ListObject object in VBA. This can be a better fit for data that grows regularly, but the code must refer to the actual table name and column. For a small, stable range, a clearly documented fixed address may be simpler.
Always make sure the set range covers the same records as the key fields. A key range that ends at row 20 and a sort range that ends at row 25 do not describe the same data block. Also check for completely blank rows inside a dataset: they can affect how a user selects a range and may signal that the input data needs cleanup before sorting.
Common Sorting Problems
- Only one column moves. The set range probably includes only the key column. Set it to the full rectangular table so the entire record moves as a row.
- The header is sorted as data. Confirm that
.Header = xlYesis set when the first row contains labels, and that the range begins on that header row. - The order looks wrong. Numbers stored as text may sort differently from numeric values, and dates stored as text may not sort chronologically. Check the underlying cell values and formats rather than assuming that display formatting converts text into numbers or dates.
- Old rules affect the result. Clear sort fields before adding the intended rules. This is especially useful when worksheet sort settings may have been changed manually.
- The macro refers to the wrong sheet. Qualify ranges with the intended worksheet, as in
ws.Range, rather than relying on whichever sheet is active when the macro runs. - Records are unexpectedly omitted. Recheck the first and last rows in the set range and key ranges. Also inspect blank rows or incomplete key cells within the data.
For text, .MatchCase = False means the sort does not treat uppercase and lowercase letters as distinct for ordering. For dates, ensure the cells contain actual Excel date values; changing the number format alone does not turn text into dates. If a sort has special needs, such as a custom order rather than ordinary alphabetical order, configure and verify that order explicitly instead of assuming the default sort will understand it.
VBA Sort Compared with Other Options
Use a VBA sort when a macro needs to rearrange worksheet data as part of a repeatable process, such as preparing a report after importing records. Use Excel’s Data > Sort command when a person wants to choose the sort interactively. It offers a visual way to add levels and inspect the selected range without writing code.
Use the worksheet SORT function when a separate, dynamically updating sorted result is useful and the Excel version supports it. Unlike the VBA sort methods above, that function spills a result into other cells and leaves the source range in place. A VBA sort changes the order of the original cells. These behaviors are different, so choose based on whether the source itself should move or a sorted view should be produced.
Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application for the code in this article. The examples use standard Excel worksheet objects and are suitable for desktop Excel on Windows; Excel for Mac also supports VBA, although some platform-specific features and shortcuts differ.
Running and Protecting a VBA Macro
On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. In Excel for Mac, Option+F11 opens the editor on many keyboards; some keyboards require Fn as well. The Developer tab can also open Visual Basic. Save a macro-enabled workbook as an .xlsm file if you need to retain the VBA code.
Before running the procedure, confirm that the sheet name and ranges match the workbook, and save a separate copy. Run the macro from the Visual Basic Editor or through Excel’s macro controls. Only enable macros in workbooks you trust; a macro can make changes beyond sorting, so review unfamiliar code before allowing it to run. Sorting itself does not offer a Ctrl+Z recovery after a macro has run.
Frequently Asked Questions
What is the VBA code to sort a range in Excel?
Use a range’s Sort method for a simple sort, or configure the worksheet’s Sort object with SortFields for more control. In either case, specify the full data range and whether it has a header.
How do I sort a column but keep rows together in VBA?
Set the sort range to include every column in the records, then use the desired column only as the key. Sorting just the key column can separate values from the names or other fields associated with them.
How do I sort in descending order in VBA?
Set the sort order to xlDescending in .SortFields.Add, or use Order1:=xlDescending with Range.Sort. For numbers, descending order puts the largest values first.
Can VBA sort by more than one column?
Yes. Add a sort field for each key before applying the sort. The first field establishes the primary order, and each following field resolves ties in the preceding fields.
Can I run a VBA sort in Excel for the web?
No. Excel for the web can display a workbook with macros, but VBA macros must be edited and run in desktop Excel. The web version has other sorting tools, but they do not execute this VBA code.
Related
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
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 Selected Cells with a MacroTo copy selected cells with VBA, call the range’s Copy method and specify a destination cell, as in…
- Zoho Sheet VBA: How to Delete the Selected Row in ExcelZoho Sheet does not run Excel VBA macros, so there is no VBA command you can paste into…
- VBA Macro to Copy and Paste a Row Below the Last RowTo copy the last data row into the row immediately below it, find the last occupied cell in…
- Excel Macro to Clear Specific Cells: Safe VBA GuideTo clear chosen cells in Excel with a macro, use VBA’s ClearContents method on a worksheet range. For…
- VBA For Loop Syntax: Examples and Common VariationsIn VBA, a For...Next loop repeats code for a counter that moves from a starting value to an…
- VBA Dictionary Count: What It Returns and How to Use ItIn VBA, read a dictionary’s item count with its Count property: dict.Count. It returns the number of key–item…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.