In Excel VBA, sort data with the worksheet’s Sort object: define the range, add one or more sort keys, set whether the range has headers, and apply the sort. The DataOption setting controls how a sort key is interpreted—for example, xlSortTextAsNumbers can sort numeric-looking text as numbers.
What the VBA DataOption setting does
DataOption is a setting for an individual sort field; it is not the option that selects ascending or descending order. Use the Order argument for direction and DataOption for how Excel handles values in that key column. The usual choice is xlSortNormal, which applies Excel’s normal sorting behavior. If a column contains numbers stored as text, xlSortTextAsNumbers tells Excel to treat those text values as numbers for sorting.
For example, text values that look like 2, 10, and 100 may sort alphabetically as 10, 100, 2. Treating numeric text as numbers produces the numeric order 2, 10, 100. This setting affects the sort operation; it does not convert the worksheet cells into numeric values. If the column mixes genuine numbers, text, blanks, and labels, inspect the results carefully, because a sort cannot resolve an unclear data convention for you.
In the object model, xlSortNormal and xlSortTextAsNumbers are common DataOption choices. These are different from constants such as xlAscending and xlDescending, which belong to the sort direction setting. A common source of confusion is trying to use a direction constant as a data option, or expecting DataOption to repair inconsistent values.
Prepare the data range before sorting
A sort should include every column belonging to each record. If a row has a date, customer, order number, and total, the sort range must cover all four columns. Sorting only the date column can detach dates from their customers and order totals. This is one of the most damaging mistakes in spreadsheet sorting, and it can be difficult to notice once rows have moved.
Decide whether the selected range includes a header row. Set Header to xlYes when the first row contains labels such as Date or Sales, and to xlNo when every row is data. Do not rely on Excel to guess correctly: a header that looks like ordinary text or a first data row that looks like a label can lead to the wrong result.
- Use a single rectangular range containing the complete records and the intended columns.
- Keep the header row inside that range when setting
HeadertoxlYes. - Check for merged cells, blank separator rows, and inconsistent values before relying on a repeated automated sort.
- Confirm that the sort key is inside the range being sorted.
When possible, format the dataset as an Excel Table with Insert > Table. Tables provide named columns and expand when new records are added, which can make the data boundary easier to manage. A macro still needs to identify the correct table and sort key, but it is less likely to rely on a hard-coded bottom row.
Sort a range with the Sort object
This example sorts an Orders worksheet by dates in column A, oldest to newest. It assumes row 1 contains headers and columns A through D make up each record. The last used row is found from column A, so that column should be populated for every order. The sort applies to all four columns, not just the date key.
Sub SortOrdersByDate()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Orders")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Stop when there are no data rows below the header.
If lastRow < 2 Then Exit Sub
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:D" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
.SortFields.Clear removes sort fields left over from an earlier sort on that worksheet. The key range starts at row 2 because row 1 is the header, while .SetRange includes the header and every record. xlSortOnValues sorts the cell values, xlAscending sets the direction, and xlSortNormal uses normal interpretation of the key values. .Orientation = xlTopToBottom sorts records vertically down the rows.
Sort by multiple columns
For a multi-level sort, add a sort field for each key, in priority order. For instance, the following procedure sorts the same A:D records by Sales in column B from largest to smallest, then by the value in column A from A to Z when sales tie. The first field is the primary key; later fields break ties.
Sub SortOrdersBySalesAndDate()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Orders")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Stop when there are no data rows below the header.
If lastRow < 2 Then Exit Sub
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:D" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Change the key ranges and directions to suit the layout, but keep each key within the overall sort range. For example, sorting first by Department and then by Last Name means that names are ordered within each department; it does not sort the entire sheet by last name first. Clear and rebuild the sort fields each time the macro runs so that an old key does not unexpectedly remain in the sort definition.
Choose a suitable sort method
The Sort object is useful when a macro needs repeatable settings, multiple keys, or an explicit range and header configuration. Excel also supports the shorter Range.Sort method for straightforward cases. For example, a qualified range can be sorted with a key and direction in one statement, but the method’s optional arguments can be harder to read and maintain as requirements grow. For a reusable macro, the longer SortFields pattern makes each choice visible.
Use normal sorting when the key values are already stored consistently as dates, numbers, or text. Choose xlSortTextAsNumbers when values such as “2” and “10” are text but should be ordered numerically. If the source data itself needs to become numeric—for calculations, for example—convert or clean the values separately rather than assuming a sort option changes their underlying type. A custom order such as High, Medium, Low also requires a deliberate custom sort order; ascending alphabetic order will not produce that business sequence.
Tables, changing row counts, and range boundaries
The sample macro finds the last row by looking upward in column A. This is simple and appropriate if every record has a value in that column. If column A can be blank near the bottom while other columns contain data, the detected range may be too short. Choose a reliably populated column, or determine the table boundary using the actual Excel Table rather than guessing from one column. Avoid a fixed range such as A1:D100 when records are regularly added; new rows below row 100 will be left out.
For a worksheet Table, use its named columns and its sort interface when the macro should follow the table as it grows. Whichever method you choose, confirm that the complete records are sorted together. Also consider totals rows, formulas, and data outside the table: a sort of the table does not necessarily sort unrelated cells elsewhere on the sheet. Test the macro on a representative copy, especially if the worksheet contains adjacent datasets.
Run VBA safely in Excel
On Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure there. Run it with the cursor inside the procedure by pressing F5, or return to Excel and use Developer > Macros. If the Developer tab is missing, enable it through File > Options > Customize Ribbon and select Developer.
Excel for Mac also supports VBA, though keyboard shortcuts can vary with the keyboard and macOS settings; use the Developer tab’s Visual Basic command if the Windows shortcut does not apply. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Save a macro-enabled workbook as .xlsm if it needs to retain its VBA project. Enable macros only in files you trust.
Save a separate copy of the workbook before running a sorting macro. Sorting changes the row order, and VBA actions cannot be undone with Ctrl+Z. A backup lets you recover if the macro targets the wrong sheet, excludes records, or sorts by an unintended key.
Common sorting problems and fixes
- The header is sorted into the records: set
.Header = xlYesand include the header row in.SetRange. - Values in other columns no longer match: expand
.SetRangeto include every column in each record before sorting. - Numbers appear in an unexpected order: check whether they are stored as text, then use
xlSortTextAsNumbersif that is the intended interpretation, or correct the source values. - New rows are not included: replace a hard-coded end row with a reliable last-row calculation or use the worksheet Table’s changing boundaries.
- The wrong worksheet is sorted: qualify ranges with a specific worksheet, as the examples do with
ws.Range, rather than depending on whichever sheet is active. - A later run sorts by an old key: clear the sort fields and add the intended fields again before applying the sort.
Blank cells, duplicate values, mixed date formats, and cells containing leading or trailing spaces can also make a result look inconsistent. A sort rearranges values according to Excel’s interpretation; it does not standardize the underlying data. When output matters, check a few rows at the top, bottom, and around ties after running the procedure.
Frequently Asked Questions
What is the VBA sort data option for numbers stored as text?
Use DataOption:=xlSortTextAsNumbers on the relevant sort field when text values that look numeric should sort numerically. It changes how the sort interprets those values; it does not convert the cells to numbers.
How do I sort in descending order in VBA?
Set the sort field’s Order argument to xlDescending. For ascending order, use xlAscending. DataOption is a separate setting and does not control direction.
How do I keep the header row at the top?
Include the header in the overall range and set .Header = xlYes. The sort key should normally start below the header, while .SetRange covers the full dataset including that first row.
Can VBA sort by more than one column?
Yes. Clear the sort fields, add a field for the primary key, then add secondary keys in priority order. Set each key’s direction and data option as needed, and apply the sort to the complete record range.
Can I run a VBA sort in Excel for the web?
No. Excel for the web can store and open macro-enabled workbooks, but VBA macros must be run in the Excel desktop application. Use Excel for Windows or Mac to execute the procedure.
Related
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Google Sheets Macro Recorder: How to Record and RunYes. Google Sheets has a built-in macro recorder: in a spreadsheet open in a desktop browser, choose Extensions…
- Excel UserForm Scrollbar: Setup, Events, and ExamplesTo use a scrollbar on an Excel UserForm, add a ScrollBar control in the Visual Basic Editor, set…
- Right in Excel VBA: Syntax, Examples, and TipsIn Excel VBA, Right returns a specified number of characters from the end of a string. For example,…
- VBA Dictionary Split: Turn Text into Keys and ValuesIn VBA, use the Split function to break a string into an array, then add the pieces to…
- VBA Range Index: How Cells, Item, and Offsets WorkIn VBA, a range index identifies a cell by its row and column position, starting at 1. For…
- Excel VBA: How to Hide a SheetTo hide a worksheet with VBA, set its Visible property to xlSheetHidden, for example: ThisWorkbook.Worksheets("Report").Visible = xlSheetHidden. Use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.