In Excel VBA, Key1 is the first column or row Excel uses to decide the sort order when you call the Range.Sort method. To sort a table by column B, for example, sort the whole table range and set Key1:=ws.Range("B1"), then specify Order1:=xlAscending or xlDescending and identify whether the range has a header.
What Key1 Means in VBA
Key1 is the first sort key: the field Excel examines first when arranging records. If the values in that field differ, their order determines where the corresponding rows go. The key does not mean “sort only this column.” The range on which you call Sort is the area Excel rearranges; the key identifies the field that controls that rearrangement.
For example, if a worksheet has names in column A, departments in B, and start dates in C, you can sort the complete table by department. Each row stays together, while the department values in column B determine the row order. Sorting only column B instead could separate departments from their associated names and dates, so the sort range should normally include every column belonging to the records.
In a call such as Range.Sort, Key1 is a named argument. The related arguments Order1, Key2, and Order2 control the direction of the first sort and, optionally, a second key. Using named arguments makes the purpose of each setting easier to see than relying on argument position.
A Basic VBA Example
This macro sorts rows 2 through 100 in a three-column table by the values in column B. Row 1 contains the headers. The example assumes that the worksheet is named Data and that the table occupies A1:C100.
Option Explicit
Sub SortByDepartment()
Dim ws As Worksheet
Dim sortRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set sortRange = ws.Range("A1:C100")
' Sort the complete table by column B, keeping row 1 as headers.
sortRange.Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom
End Sub
The key points are that sortRange covers the full table, while Key1 points to the field in column B. Order1:=xlAscending sorts text A to Z and numbers from low to high. Use xlDescending for Z to A or high to low. Header:=xlYes tells Excel not to treat the first row as a record to rearrange.
Before running any macro that rearranges data, save a copy of the workbook. VBA macro actions cannot be undone with Ctrl+Z, so a backup is the reliable way to restore the original order if the result is not what you intended.
Choosing the Sort Range and Key
The range receiving the Sort call and the key range serve different purposes. The receiving range defines which cells move; the key identifies the column or row used to rank them. They should refer to the same worksheet, and the key should be in the part of the data being sorted. A key reference can be a cell in the field’s column, such as B1 in the example, rather than a separate list of values.
When choosing the sort range, include all columns that belong to each record and exclude unrelated notes or totals. If a table extends from A1 to F250, sorting only A1:C250 would move the first three columns while leaving the remaining fields behind. That creates mismatched records even though the sort itself may run without an error.
- Include the header row in the sort range when you want to set
Header:=xlYesexplicitly. - Include all record columns so each row stays intact as it moves.
- Use a key within the table and check that it represents the field you intend to sort by.
- Set the last row deliberately if the data range changes, or use a structured Excel table when the data grows regularly.
For a range without headers, set Header:=xlNo. Although xlGuess is available, relying on automatic detection can make a macro’s behavior less predictable, especially when the first data row resembles a heading. An explicit setting documents the expected layout and avoids that ambiguity.
Sort Direction and Orientation
Order1 controls the direction for Key1. The common choices are xlAscending and xlDescending. For ordinary text, ascending means alphabetical order; for numeric values, it means smallest to largest; for dates, it means earliest to latest. Descending reverses those sequences. Excel also applies its own ordering rules to blanks and special values, so check the result if those values matter to a report.
Orientation determines whether Excel sorts down a column or across a row. For a conventional table, use xlTopToBottom, which sorts records vertically. Use xlLeftToRight only when the data is arranged horizontally and the items to reorder are columns. Choosing the wrong orientation can produce a valid sort in the wrong direction relative to the layout.
The argument names in the example are useful because they make the settings explicit. Excel’s VBA editor can be opened from the Developer tab’s Visual Basic command; on Windows, Alt+F11 is also a common shortcut. On Mac, use the Visual Basic command on the Developer tab, since keyboard combinations can vary with the keyboard and system settings.
Sorting by More Than One Column
A second key resolves ties in the first key. For example, sorting first by department and then by employee name groups employees by department while alphabetizing names within each group. In a direct Range.Sort call, add Key2 and Order2. The following example uses the same A1:C100 layout, with departments in B and names in A.
Sub SortByDepartmentThenName()
Dim ws As Worksheet
Dim sortRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set sortRange = ws.Range("A1:C100")
' Department is the primary key; name breaks ties.
sortRange.Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, _
Key2:=ws.Range("A1"), _
Order2:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom
End Sub
The first key has priority: Excel sorts by department before it considers the name. A third key can be supplied with Key3 and Order3, for example to order employees with the same department and name by date. If the sort needs more levels or more detailed options, the worksheet’s Sort object and its SortFields collection provide a more configurable approach. That style sets up each key separately, then applies the sort to a defined range.
Range.Sort Compared with the Sort Object
Range.Sort is concise and well suited to straightforward sorts with up to three keys. Its arguments sit together in one method call, which is convenient for a short macro. For repeatable reports with several sort levels, custom lists, or options that need to be configured individually, the worksheet Sort object can make the setup easier to maintain.
With the Sort object, code typically clears existing sort fields, adds one or more fields with SortFields.Add, sets the overall range and header behavior, and calls Apply. The key ranges for that pattern are usually the data cells in each field, excluding the header. Do not mix the two patterns casually: Range.Sort takes a key as an argument, whereas the object approach builds a collection of sort fields before applying the sort.
For occasional manual sorting, Excel’s Data tab provides sorting commands and a Sort dialog where multiple levels can be specified. VBA is useful when the same sort must be repeated, triggered by a button, or incorporated into a larger process. Regardless of the method, the essential decision is the same: define the complete records to move and the fields that determine their order.
Common Problems and How to Avoid Them
- Only one column moves: The range passed to
Sortmay cover only the key column. Expand it to include every column in the records. - The header gets sorted into the data: Set
Header:=xlYeswhen the first row contains labels, orxlNowhen it contains data. Avoid guessing if the layout is known. - The wrong field controls the order: Check the column referenced by
Key1. A key in column C will sort by C, even if the range begins in column A. - Numbers appear out of numeric order: Some entries may be numbers stored as text. Standardize the data types before sorting if numeric order is required.
- The order is unexpected for dates: Text that looks like a date may not be stored as an actual Excel date value. Check the underlying cell values and formats.
- The macro errors or affects a different sheet: Qualify worksheet and range references, as the examples do, instead of depending on whichever sheet happens to be active.
Also inspect the range for blank rows, merged cells, totals, or notes positioned beside the table. Such layout features can change what Excel treats as part of a contiguous data block or make the intended records harder to identify. A clearly defined table range is safer than relying on the active cell or an implicit current region in a macro that must work repeatedly.
When Excel Tables Are a Better Fit
If records are added or removed frequently, converting the data to an Excel table can reduce the maintenance required to update a fixed address such as A1:C100. Tables expand as rows are added, and their header names make the data structure clearer to people maintaining the workbook. VBA can still sort a table, but the macro should refer to the table’s range and the appropriate table column rather than assume that the original final row never changes.
Tables are especially helpful when a worksheet contains other information nearby, because the table marks the intended data block. They do not remove the need to choose the correct sort field or preserve a backup before running a macro. They simply make the range boundary easier to manage as the dataset changes.
Frequently Asked Questions
Is Key1 required in VBA Sort?
For a useful, clearly specified Range.Sort call, provide Key1 to identify the primary field. The method’s arguments are optional in VBA’s syntax, but omitting the key does not clearly express the sort you want and can lead to confusing results.
Should Key1 point to the header or the first data cell?
It is common to point to a cell in the key column, such as B1 in a range whose first row is a header, while setting Header:=xlYes. The header setting tells Excel how to treat the top row. Keep the key on the same worksheet and within the field being sorted.
Does Key1 sort the entire row?
No. Key1 identifies the field used to decide order. The range receiving the Sort call determines which cells move. Include all columns that belong to each record so the rows remain intact.
How do I sort Z to A?
Set Order1:=xlDescending for a text key. The same direction puts numeric values high to low and dates latest to earliest.
Can I sort by two columns with Key1?Key1 is only the primary key. Add Key2 and Order2 to sort ties in the first field by another field. For more configurable multi-level sorting, use the worksheet Sort object and its SortFields collection.
Related
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA GoTo: Syntax, Examples, and Error HandlingIn Excel VBA, GoTo transfers execution to a named label elsewhere in the same procedure. It is most…
- VBA Code for UserForm: Build an Excel Data Entry FormTo create a VBA UserForm in Excel, add a UserForm in the Visual Basic Editor, place controls on…
- Excel VBA to Send Email in Outlook 365: Setup and CodeYes. In Excel for Microsoft 365 on Windows, VBA can create and send an email through the classic…
- Excel VBA Send Email in New Outlook: What Actually WorksExcel VBA cannot directly automate the new Outlook for Windows: the new app does not provide the classic…
- Excel Macro Recorder: Keyboard Shortcuts and SetupYes. In Excel for Microsoft 365 on Windows, record a macro from Developer > Code > Record Macro…
- LibreOffice Macro Recorder: Setup and LimitsYes—LibreOffice includes a built-in macro recorder, but it is limited: it captures some actions in Calc and Writer…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.