In VBA, a sort key is the cell or range that tells Excel which column’s values to use when ordering rows. In a simple sort, set it with Key1:=; for more control or multiple levels, add key ranges to a worksheet’s SortFields collection.
What a VBA sort key means
A sort key is the field Excel compares to decide the order of records. If a worksheet has customer names in column A, regions in B, and sales in C, then column B can be the key for sorting the rows by region. The key identifies the sort column; it does not mean Excel sorts only that column. To keep each customer’s region and sales attached to the right customer, the range being sorted must include the whole table.
In the older, compact Range.Sort method, the first key is supplied as the named argument Key1. Excel also supports Key2 and Key3 there. In the worksheet Sort object, keys are added as sort fields, which makes it easier to specify options and build sorts with several levels. In either approach, the key is a range reference, not the text name of a column or a value such as “ascending.”
A key can sort numbers, dates, text, or other comparable cell values. The order is specified separately, commonly with xlAscending or xlDescending. For example, sorting by a date key in ascending order places earlier dates before later ones; sorting by a text key in ascending order generally places values alphabetically.
How Excel uses the key
Excel evaluates the key values for the records in the sort range, then rearranges the rows or columns in that range according to those values. The key should point to the intended field inside the range being sorted. With a header row, the sort range includes the header, while the key reference should identify the column to sort by. Set the header option correctly so the heading is not treated as an ordinary record.
Imagine a table in A1:C4 with headings Name, City, and Sales, followed by three customer records. If City is the key, Excel compares the City entries and moves each complete row so the names and sales remain paired. If you sort only B1:B4 instead, you move the city values without moving the rest of the records. That is a common cause of corrupted tables.
The key’s position matters as well. A key in column B can sort a range that spans A:C, because B is one of the fields in that range. A key in a separate, unrelated range is not a reliable way to sort the table and may produce an error or unintended result. Keep the key and the records being sorted aligned row by row.
Basic Key1 example
This example sorts a fixed table in A1:C100 by the values in column B, from A to Z. The first row contains headers, so Header:=xlYes tells Excel to leave it at the top. The one-cell key reference in B2 identifies the sort field; the full A1:C100 range is what Excel rearranges.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.
Sub SortByCity()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Sort the complete table by its City field.
ws.Range("A1:C100").Sort _
Key1:=ws.Range("B2"), _
Order1:=xlAscending, _
Header:=xlYes
End Sub
Change the worksheet name and range to match the workbook. If the table has no header row, use Header:=xlNo. If the data is in descending order, change the order argument to xlDescending. The key reference is a worksheet range, so it should be qualified with the worksheet variable, as in the example, rather than relying on whichever sheet happens to be active.
Using SortFields for multiple keys
When a sort needs more than one level, use the worksheet’s SortFields collection. The first added field is the primary key. Excel sorts by it first; when two records tie on that field, it compares the second key, then the next one if needed. For instance, sorting first by department and then by hire date groups employees by department and orders each group by date.
This example assumes headers in row 1, departments in B, and dates in C. It finds the last used row by looking upward from the bottom of column A, then applies the sort to the full A:C table. The data should have no gaps in column A before the final record, or use a more appropriate column to find the last row.
Save a copy of the workbook before running this macro; macro actions cannot be reversed with Ctrl+Z.
Sub SortByDepartmentAndDate()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("C2:C" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:C" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Here, department is the primary key and hire date is secondary. Within each department, the dates are sorted newest to oldest because the second field uses descending order. Clear existing sort fields before adding new ones; otherwise, a sort previously configured on the same sort object can remain part of the operation.
Choosing the right key and range
Before writing the sort, decide which values define the order and which records must move together. The best key is usually one field in the table, but a table can have several keys when ties need a consistent order. A few checks help prevent subtle mistakes:
- Include every related column. Set the sort range to the whole dataset, not just the key column.
- Use the correct header setting. Choose
xlYeswhen the first row contains field names andxlNowhen it contains data. - Match the key rows to the records. A key range for a multi-field sort should cover the data rows, not a different section of the sheet.
- Choose a tie-breaker if order matters. A secondary key can sort equal primary-key values predictably.
- Check the value types. Dates or numbers stored as text may sort in an unexpected order because Excel is comparing text rather than true date or numeric values.
For a range with a title above the table, exclude the title from the sort range. For example, if the headings are in row 3 and records begin in row 4, the sort range can begin at row 3 and use Header = xlYes; the data key can begin at row 4. If there are blank rows inside the records, decide whether they are intentional separators. A sort may move blank rows along with the data or treat separate blocks differently than expected, so a contiguous table is generally easier to manage.
Headers, tables, and dynamic data
Fixed addresses such as A1:C100 are straightforward for a small, stable list, but they can omit new records or include empty rows as the list changes. A macro can calculate a final row, as the multi-key example does. The column used to find that row should be populated for every record; otherwise, a blank in that column can make the detected range end too early.
Excel Tables are another practical option for growing datasets. A table expands when new records are added, and its headers make fields easier to identify in code. The sorting operation still needs to apply to the table’s data, not to an isolated key column. If the workbook already uses a table, consider referencing its DataBodyRange or using the table’s sort interface so newly added rows are included. Be deliberate about whether the header row is part of the range and whether the table is empty, since an empty table has no data body to sort.
For a basic range sort, Windows users commonly open the Visual Basic Editor with Alt+F11. On Mac, Option+F11 may open it; on some keyboards, Fn may also be needed. The Developer tab’s Visual Basic command is an alternative where the tab is available. Excel for the web can open workbooks containing VBA but cannot run or edit VBA macros; use desktop Excel for this code.
Common sort-key problems
Only one column moves. The sort range probably covers only the key column. Expand it to include every field in each record. Sorting a partial range can misalign names, IDs, dates, and amounts.
The heading is sorted with the data. Set the header option correctly. With Range.Sort, use the Header argument; with SortFields, set the sort object’s Header property before applying it.
The result is not truly chronological or numeric. Some entries may be stored as text, or a column may contain mixed types. Check representative cells and normalize the data before sorting. For example, text values like “10” and “2” can sort alphabetically rather than in numerical order.
The macro sorts the wrong sheet. Unqualified references such as Range("A1:C100") use the active sheet. Qualify ranges with a worksheet variable, such as ws.Range(...), so the macro acts on the intended sheet. Also verify the worksheet name exactly matches the tab name.
A second sort behaves differently than expected. With the SortFields method, clear old fields before adding the intended keys. Also check whether another macro or a user action changed the sort range, header setting, or order. Sorting does not automatically make an inconsistent dataset consistent; duplicate values and blanks still need a defined policy.
VBA sorting versus worksheet sorting
The regular Excel Sort command is often the best choice for a one-time operation because it displays the selected fields and order before changing the sheet. On Windows Excel for Microsoft 365, select a cell in the dataset and use Data > Sort; the dialog lets you add levels and identify whether the data has headers. The menu path and dialog are similar in desktop Excel for Mac, though the ribbon layout and keyboard shortcuts can differ. Excel for the web offers sorting from its ribbon, but not VBA execution.
VBA is useful when sorting is a repeatable part of a workflow: for example, after importing a monthly file or before generating a report. A macro can apply the same range, keys, and directions each time. It also makes mistakes repeatable if the assumptions are wrong, so validate the worksheet, table boundaries, and header choice before running it. VBA sorting rearranges cells; it is not the same as a formula that displays a separately sorted result.
Frequently Asked Questions
What does Key1 mean in Excel VBA?
Key1 is the primary sort field supplied to the Range.Sort method. It tells Excel which column’s values to compare first; Order1 separately specifies ascending or descending order.
Can a VBA sort have more than one key?
Yes. The compact Range.Sort method accepts up to three key arguments: Key1, Key2, and Key3. For more configurable multi-level sorts, use SortFields.Add to add the primary and secondary fields.
Does the sort key have to be a whole column?
No. A key can be a single cell identifying the field in a Range.Sort call, or a range covering the data values in that field when using SortFields. The key must correspond to the records being sorted, and the full dataset should be included in the sort range.
How do I sort descending with VBA?
Set the order for the key to xlDescending. In a Range.Sort call, that is typically the Order1 argument; with SortFields.Add, use the Order argument for that field.
Can I undo a VBA sort with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, so a macro sort cannot generally be reversed with Ctrl+Z. Save a separate copy or use a controlled backup before running a macro that rearranges important data.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Goal Seek: Automate What-If AnalysisTo run Goal Seek in Excel VBA, call the GoalSeek method on a cell containing a formula, and…
- 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 Mac VBA: What Works and How to Use ItYes—Microsoft Excel for Mac supports VBA, so you can create, edit, and run many Excel macros on a…
- Excel VBA Copy and Paste a Range to Another WorksheetTo copy a range to another worksheet with Excel VBA, identify the source and destination worksheets, then use…
- VBA Jump to Next Loop: How to Skip an IterationVBA has no built-in Continue For or Continue Do statement. To jump to the next loop iteration, put…
- How to Turn On Excel’s Macro Recorder in Microsoft 365In Excel for Microsoft 365 on Windows, select Developer > Code > Record Macro, set the macro’s name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.