Sort.Apply is the VBA method that runs a sort after you have configured its range, sort keys, and options. In Excel for Microsoft 365 on Windows, set those properties on a worksheet’s Sort object, then call .Apply; setting up the sort alone does not rearrange the data.
What VBA Sort.Apply Does
Excel’s Sort object holds the instructions for a sort: which cells to sort, which columns or rows determine the order, whether the data has headers, and how to handle text and direction. The Apply method carries out those instructions on the specified range. It does not take a column or order as an argument, so a call such as Sort.Apply is not enough on its own.
A typical sort has three parts: clear any old sort fields, add one or more new fields, and set the range and options before applying. This approach is useful for repeatable or multi-column sorts, and it makes each important setting visible in the macro. If you only want a quick sort in a short macro, Excel also has the older Range.Sort method, but the Sort object is clearer when you need several keys or more control.
How to Configure a Sort
In the example below, a worksheet named Sales has headers in row 1 and records in columns A through D. Column A contains an ID, column B contains a customer name, and columns C and D contain other record details. The macro finds the last used row by looking up column A, then sorts the entire table by customer name in ascending order. Change the sheet name and column references to match your workbook.
Save a copy of the workbook before running a macro: sorting changes the data, and macro actions cannot be undone with Ctrl+Z. To add the code, open the Visual Basic Editor from Developer > Visual Basic, choose Insert > Module, and paste it into the module. If the Developer tab is hidden, enable it in Excel’s ribbon settings.
Sub SortSalesByCustomer()
' Sort the complete Sales table by its customer-name column.
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sales")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "There are no data rows to sort."
Exit Sub
End If
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:D" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
The key range starts at B2 because row 1 is a header; the full range passed to SetRange starts at A1 so the header and every field in each record are included. .Header = xlYes tells Excel not to sort the first row as a customer record. Finally, .Apply executes the configured sort.
Choosing the Correct Range and Header Setting
The most consequential decision is often the sort range, not the sort direction. The range must cover every column that belongs to each record. If you sort only the customer-name column while leaving the other columns in place, names can become detached from their IDs and sales details. For a table in A1:D100, sort A1:D100 using a key in the relevant column; do not apply the sort to just B2:B100.
Set the header option to match the selected range. Use xlYes when the first row contains labels, xlNo when every row is data, or xlGuess only if you want Excel to infer the setting. Explicitly choosing yes or no is more predictable in an automated task. If your data starts on a different row or spans more columns, update both the key and the range consistently.
The sample finds its last row using column A. That works when each record has an ID in A. If column A can be blank for some records, the detected endpoint may be too early; use a column that is reliably filled, or determine the table boundaries another way. Excel Tables are another useful option: their structured ranges expand as records are added, which can make recurring sort macros easier to maintain.
Sorting on More Than One Column
For a multi-level sort, add fields in priority order. For example, to sort sales records first by region and then by customer, add the region field before the customer field. Excel groups matching regions together, then sorts the customers within each region. Every key must refer to a column or row within the overall range being sorted.
To adapt the sample, clear the fields as before, then add the region key followed by the customer key. For instance, if region is in column C and customer is in B, add the C key first and the B key second, each with its own ascending or descending order. Keep the same full-table SetRange and header setting. A common mistake is to reverse the priority: the first key is the main grouping order, while later keys resolve ties within it.
Sort direction is specified per field with Order:=xlAscending or Order:=xlDescending. For dates and numbers, ascending generally means earliest-to-latest or smallest-to-largest; descending reverses that order. Text sorts alphabetically in ascending order, subject to Excel’s sorting rules and the workbook’s language settings.
Sort Object Compared with Range.Sort
Worksheet.Sort is a configurable object: its SortFields collection can hold multiple keys, and its properties make header handling, orientation, and comparison options explicit. That makes it a good fit for macros that must consistently sort a defined table or use several levels of priority. Calling .Apply is the final step that runs those settings.
Range.Sort is a method called directly on a range and can be convenient for a compact, one-off sort with a small number of keys. It accepts key and order arguments as part of the method call. It is not a different kind of sorting result; both approaches rearrange cells in Excel. Choose based on readability and the options you need, and avoid mixing the two approaches in one procedure unless there is a clear reason.
Neither method automatically makes an incomplete range safe. The macro still needs to identify the full set of related records and whether the first row is a header. Before running a new sort routine on important data, check its range and key references against the worksheet layout.
Common Problems and How to Prevent Them
- The macro runs but data does not change: Confirm that the sort fields were added, the range was set, and the code reaches
.Apply. A configured sort is not executed until the method is called. - Headers move into the records: Set
.Header = xlYeswhen the range includes a header row. If the range begins at the first data row instead, set the option toxlNo. - Values no longer match their records: Expand
SetRangeto include every related column. Sorting one field by itself can break row relationships. - Some records are missing: Check how the macro finds the last row. An empty cell in the column used for the endpoint can make the detected range too short.
- The order looks unexpected: Check whether values are stored consistently as numbers, dates, or text. Numbers stored as text may not sort like numeric values, and mixed data can produce surprising results.
- The macro reports a missing worksheet: Confirm the sheet name exactly matches the workbook tab.
ThisWorkbookrefers to the workbook containing the code, which may differ from the active workbook.
For recurring work, test with a copy that includes representative blanks, duplicate keys, and different data types. A sort does not remove duplicate records: it only changes their order. If the outcome depends on how equal values are ordered, add a further key, such as a unique ID, to make the intended order explicit.
Running Macros and Platform Notes
In Excel for Microsoft 365 on Windows, VBA macros can run from the Visual Basic Editor or through a button or other macro command in the workbook. The workbook must be saved in a macro-enabled format such as .xlsm if it needs to retain VBA code. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop app for this procedure.
Excel for Mac also supports many VBA procedures and the Excel sort object, though the Visual Basic Editor and some security or interface steps differ from Windows. Use Developer > Visual Basic if the Developer tab is available, and confirm that a particular workbook’s code is compatible with Mac before relying on it. Keyboard shortcuts can also differ by keyboard and system settings, so using the ribbon route is a dependable way to find the editor.
Only run macros from sources you trust. On Windows, macro security settings are in File > Options > Trust Center > Trust Center Settings > Macro Settings; organizational policies may control these options. Do not enable all macros globally just to run one sort. Keep a backup, review the code, and use a trusted location or approved signing process if your organization requires it.
Frequently Asked Questions
What does VBA Sort.Apply do?Sort.Apply runs the sort settings already configured on a worksheet’s Sort object. It does not specify the key or range by itself.
Do I need to clear SortFields before adding a sort?
It is usually sensible to call .SortFields.Clear before adding fields in a reusable macro. That prevents sort keys left on the sort object from an earlier configuration from affecting the next run.
Does Sort.Apply sort just the key column?
No. The key determines the ordering, while SetRange identifies the cells Excel rearranges. Include the complete records in that range so each row’s values stay together.
Can VBA sort by two columns?
Yes. Add two sort fields to SortFields, in priority order, then set the complete range and call .Apply. The second field sorts records that tie on the first field.
Can I undo a VBA sort with Ctrl+Z?
No. Excel clears its standard undo history when a macro runs, so Ctrl+Z cannot reverse the macro’s sort. Save a copy or use a separate backup before running code that rearranges important data.
Related
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Find Text in a String: InStr ExplainedIn Excel VBA, use the InStr function to find one piece of text inside another: it returns the…
- Google Sheets Macros: How to Create, Run, and Edit ThemYes. You can create macros in Google Sheets using its built-in recorder: in a desktop web browser, choose…
- VBA Cell to Range: How to Reference Cells in ExcelIn Excel VBA, a cell is already a one-cell Range object: use Set target = Worksheets("Sheet1").Range("A1") to store…
- VBA MsgBox OK Only: Syntax and Button OptionsYes. In Excel VBA, MsgBox displays an OK-only dialog by default, and you can make that choice explicit…
- VBA Find: Search Excel Ranges and Handle Results SafelyIn Excel VBA, Find is a method of a Range, not a separate object: it searches a specified…
- Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.