In Excel VBA, the basic sort syntax is Range.Sort: specify the range to rearrange, a key column, its sort order, and whether the range has a header. For multiple sort levels or more control, use the worksheet’s Sort object with SortFields.
The basic VBA sort syntax
Range.Sort is the shortest way to sort a rectangular block of cells. In a call, the range before .Sort is the entire block that Excel may rearrange; Key1 identifies the first sort field. Order1 sets that field to ascending or descending, and Header tells Excel whether the first row contains column labels.
A simplified pattern is someRange.Sort Key1:=keyCell, Order1:=xlAscending, Header:=xlYes. Named arguments make the call easier to read and avoid relying on the order of optional arguments. Common order constants include xlAscending and xlDescending; header constants include xlYes, xlNo, and xlGuess. For predictable results, specify the header setting rather than asking Excel to guess.
VBA macros can rearrange workbook data, and sorting cannot be reversed with Ctrl+Z after a macro runs. Save a copy of the workbook before running any of the examples below. On Windows, VBA code runs in desktop Excel; Excel for the web cannot run VBA macros. Excel for Mac supports VBA, although macro security and some application details can differ.
Sort one column while keeping rows together
Usually, you do not want to sort one column independently: that would separate values from the rest of their records. Instead, sort the full data range and use a cell in the desired key column. This example sorts a four-column list by the values in column B, largest to smallest. It assumes row 1 contains headings and rows 2 through 100 contain records.
Sub SortBySales()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Sort each complete record by the value in column B.
ws.Range("A1:D100").Sort _
Key1:=ws.Range("B2"), _
Order1:=xlDescending, _
Header:=xlYes
End Sub
The call rearranges rows across columns A through D. It does not sort only the cells in column B. The key cell, B2, points to the field to use; Excel applies that field’s sort order to the rows in the specified range. If the data has no header, use Header:=xlNo and include every data row in the range.
To sort names alphabetically, change Order1 to xlAscending. The same basic pattern works for numbers, dates, and text, provided the cells contain values Excel can interpret consistently. Text that looks like a number may sort differently from an actual numeric value, a common issue covered below.
Sort by more than one column
Use the Sort object when you need a primary key and a tie-breaker, or want to set several options explicitly. Clear existing sort fields before adding yours; otherwise, a worksheet’s prior sort configuration can remain in place. Each key should describe the corresponding field in the data, and SetRange should cover the full records to move.
This example sorts a list first by department in column B, A to Z, then by sales in column C, highest to lowest. The heading is in row 1 and the data runs from rows 2 to 100.
Sub SortByDepartmentThenSales()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Define the primary and secondary sort fields.
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B100"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("C2:C100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:D100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Excel groups matching departments together, then orders sales within each department. The sort-field keys exclude the header row, while SetRange includes it because .Header = xlYes identifies the first row as labels. Add further SortFields.Add calls for additional tie-breakers, in the order Excel should apply them. A third key, for example, is considered only when the first and second keys are equal.
Use a range that grows with your data
A fixed address such as A1:D100 is straightforward, but it misses later rows and may include empty rows if the list changes. For a simple contiguous list, find the last used row in a reliable key column, then build the sort range from that result. This example assumes column A is populated for every record and row 1 contains headings.
Sub SortGrowingList()
Dim ws As Worksheet
Dim lastRow As Long
Dim sortRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last populated cell in the record ID column.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set sortRange = ws.Range("A1:D" & lastRow)
' Sort the complete list by column B, ascending.
sortRange.Sort Key1:=ws.Range("B2"), _
Order1:=xlAscending, Header:=xlYes
End Sub
The If check prevents a header-only list from being treated as a normal data set. The last-row method depends on the chosen column being filled for each record; if column A has gaps, it may stop too early. Choose a consistently populated column, or use an Excel table when records are regularly added. If the data spans multiple blocks or has notes below it, identify the intended range carefully rather than assuming the last used cell on the sheet marks the list’s boundary.
Headers, direction, and other sort settings
For a basic single-key sort, the settings most readers need are the key, direction, and header flag. In a multi-key sort, the Sort object exposes additional choices. Set options deliberately when the data’s layout or matching rules matter.
Header: usexlYeswhen the first row of the sort range contains headings, orxlNowhen it contains data.xlGuessleaves the decision to Excel and can be ambiguous.Orientation:xlTopToBottomsorts rows by columns, the usual arrangement for lists.xlLeftToRightsorts columns by rows and is for data laid out horizontally.MatchCase: set toTrueif capitalization should affect text ordering; otherwise, useFalse.SortOn: in a sort field,xlSortOnValuessorts cell values. Other sort behaviors are available for features such as cell or font color.DataOption:xlSortNormaluses the normal interpretation of the values.xlSortTextAsNumbersis useful when numbers are stored as text and should be treated numerically.
These settings do not repair inconsistent source data. A column containing a mix of true dates, date-like text, blanks, and errors can still produce results that surprise readers. Normalize the values first if the intended order is important, then verify the result on a copy of the workbook.
Sorting Excel tables and filtered lists
Excel tables are often a better foundation than fixed ranges for lists that grow. A table expands as rows are added and carries its headers and structured data together. In VBA, a table is represented by a ListObject; its Sort property provides a sort object. You can build sort fields from the relevant table columns and apply the sort to the table rather than guessing its changing final row.
When working with a table, make sure the key belongs to the table column and that the intended operation sorts table records, not unrelated cells beside the table. The table’s filter dropdowns and Excel’s Data sort commands are also practical when a one-time sort is all that is needed. Sorting a filtered list can interact with the visible records and the underlying table; check the resulting records and retain a backup when the distinction matters.
A regular range with AutoFilter arrows is not necessarily the same object as an Excel table. If the data is just a range, specify the complete range and header setting explicitly. Avoid selecting only the key column when other columns hold related information.
Common problems and how to prevent them
Most VBA sorting errors come from a mismatch between the range, key, and data structure rather than from the sort command itself. Check these points when the macro runs but the output is wrong:
- Rows became mismatched: the sort range may include only the key column. Expand it to cover every field in each record.
- The heading moved into the data: set
Header:=xlYesor.Header = xlYeswhen the first row is a header. Do not include a title row above the actual table unless it is handled separately. - New records were not sorted: replace a stale fixed endpoint with a last-row calculation or use a table that grows with its data.
- The order seems alphabetical rather than numeric: some values may be numbers stored as text. Convert them to numbers or use an appropriate data option after checking the column’s contents.
- The wrong worksheet was sorted: qualify ranges with a worksheet variable, as in
ws.Range(...), instead of relying on whichever sheet is active. - Several sort keys behave unexpectedly: clear old sort fields, add the keys in priority order, and ensure
SetRangeincludes every key column and every record.
Also check for merged cells or inconsistent row layouts in the target range. Sorting works best with a rectangular list where each row represents one record and each column represents one field. A macro should not silently assume that a worksheet’s current selection is the data set; explicit, qualified ranges make the operation easier to review and safer to repeat.
Range.Sort or the Sort object?
Use Range.Sort for a compact one-key operation with familiar defaults. Its named arguments keep the code short, and it is a good fit for a small macro that sorts a known block once. It can also accept additional keys and settings, but a long call becomes harder to read as requirements grow.
Use the Sort object for multiple keys, explicit sort options, or code that needs to manage sort fields systematically. Its steps are more verbose: clear fields, add them, set the full range and header behavior, then apply. That extra structure makes the priority order and configuration visible. Neither approach is universally better; choose the shortest one that expresses the actual sort reliably.
If a sort is needed only occasionally, use Excel’s Data > Sort command or a table’s header controls instead of writing a macro. For a formula-driven sorted view that leaves the original data untouched, Excel for Microsoft 365 and Excel 2021 also include the worksheet SORT function. That is a different tool from VBA’s Range.Sort: the function returns sorted results in the grid, while VBA rearranges the cells in place.
Frequently Asked Questions
What is the simplest VBA code to sort a range?
Use Range.Sort with a key, order, and header flag. For example, sort a complete range by one column with myRange.Sort Key1:=myKeyCell, Order1:=xlAscending, Header:=xlYes. Qualify both ranges with the worksheet for dependable results.
How do I sort by two columns in VBA?
Use a worksheet’s Sort object, clear its existing fields, add one SortFields.Add entry for each key in priority order, set the full range and header option, then call .Apply. The first field is the primary sort; later fields break ties.
Does VBA sort the whole row automatically?
Only within the range you tell Excel to sort. Include all columns belonging to each record in that range; otherwise, values outside it will not move with the key column.
Can I sort without a header row?
Yes. Set Header:=xlNo for Range.Sort, or .Header = xlNo for the Sort object, and define the range to include only the data you want reordered.
Can I undo a VBA sort with Ctrl+Z?
No. Running a macro clears Excel’s normal undo history, so Ctrl+Z will not restore the pre-sort order. Save a separate copy or preserve the original data before running a sorting macro.
Related
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Global Variables: Scope and ExamplesIn Excel VBA, a global variable is a value that procedures throughout the same VBA project can access.…
- VBA Code to Update a PivotTable in Excel AutomaticallyTo update one PivotTable in Excel for Microsoft 365 on Windows, call its RefreshTable method from a VBA…
- 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 AutoFilter Criteria1: Syntax, Examples & Tips in ExcelIn VBA, Criteria1 is the first filter condition passed to Excel’s AutoFilter method; for example, Criteria1:="East" keeps rows…
- Excel VBA: Copy and Paste a Dynamic Range AutomaticallyTo copy a range that grows or shrinks, use VBA to find its last populated row and column,…
- VBA MsgBox Line Breaks: Constants, Examples, and FixesTo add a line break to a VBA MsgBox message, join the text with vbCrLf, as in MsgBox…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.