To keep a header row out of the sorted data in Excel VBA, set the sort option to Header:=xlYes. For example, sort a range by its second column with Range("A1:D20").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes; Excel treats the first row of that range as column headings rather than a record.
What the VBA header setting means
The Header argument tells Excel how to interpret the first row or column of the range being sorted. For the usual top-to-bottom worksheet sort, use one of these constants:
xlYes: The first row of the selected range contains headings and should stay above the records.xlNo: The selected range contains data only; sort every row in it.xlGuess: Let Excel try to determine whether the range has a header.
For a macro that must produce a predictable result, choose xlYes or xlNo explicitly. xlGuess can be convenient for an interactive task, but it relies on Excel interpreting the data correctly. A row of text values can look like a header to a person while still being part of the data, or a heading can resemble an ordinary value. Being explicit avoids that uncertainty.
The header setting does not create headings, name columns, or decide which column to sort. It only describes the first row of the range you give to the sort operation. The range boundary and the sort key still matter.
A basic VBA example for a worksheet range
This macro assumes the worksheet named Sales has headings in A1:D1, records below them, and a value in column A for every record. It sorts the full A:D record range by column B in ascending order. Change the worksheet name, range columns, and sort key to fit the workbook.
Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z in Windows Excel or Command+Z in Mac Excel.
Sub SortSalesByColumnB()
' Assumes headers in row 1 and records in columns A:D.
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sales")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Do nothing if the sheet contains only a header or is empty.
If lastRow < 2 Then Exit Sub
' Sort complete records; keep the first row as the header.
ws.Range("A1:D" & lastRow).Sort _
Key1:=ws.Range("B2"), _
Order1:=xlAscending, _
Header:=xlYes
End Sub
The code finds the last used row by looking upward from the bottom of column A. This is a practical way to size a range, but it depends on column A being filled for every record. If records can have blanks in A, use a column that is reliably populated, or determine the last row from another dependable field. If the data starts on a different row, update both the range and the header assumption.
Choose the whole record range, not just the sort column
A frequent sorting mistake is to select only the column used as the key. If you sort column B by itself while the related values in columns A, C, and D remain where they were, names, dates, and amounts can become mismatched. Instead, sort the entire rectangular block of records, with the key pointing to the column that determines order.
For instance, if A contains customer names, B contains regions, C contains order dates, and D contains sales, sorting A1:D100 by B2 moves each row together. If you use a range such as B1:B100 instead, only the region values move. That may be appropriate for a standalone list, but it is usually wrong for a multi-column table.
Also check that the selected range includes the actual header row. If the range begins at row 2, then its first row is a data row, and Header:=xlYes would incorrectly keep that record at the top. Conversely, if the range starts at row 1 but row 1 is data, xlNo is the correct setting.
Use a table when the data grows
An Excel Table can make sorting easier to maintain when records are regularly added or removed. Tables have named columns, expand as new rows are entered, and carry their header row as part of the table structure. In desktop Excel for Microsoft 365 on Windows, create one by selecting the data and choosing Insert > Table, then confirm that the table has headers. You can also use Ctrl+T on Windows; on Mac, use Command+T.
For a table named SalesTable, the following macro sorts its Sales column from largest to smallest. It finds the table by name rather than assuming that it will always occupy the same worksheet cells.
As with any macro that changes the workbook, save a separate copy before running it; the sort cannot be undone using Excel’s normal undo shortcut.
Sub SortTableBySales()
' Assumes a table named SalesTable with a Sales column.
Dim ws As Worksheet
Dim lo As ListObject
Set ws = ThisWorkbook.Worksheets("Sales")
Set lo = ws.ListObjects("SalesTable")
With lo.Sort
.SortFields.Clear
.SortFields.Add Key:=lo.ListColumns("Sales").DataBodyRange, _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SetRange lo.Range
.Apply
End With
End Sub
The column name in ListColumns("Sales") must match the table heading. A misspelled name or a table that does not exist will cause a VBA error, so verify both names in the workbook. The table’s header is not a data record; specifying xlYes makes that intention clear in the sort settings.
Sort by more than one column
Sorting by a single key is not always enough. To group records by region and then order each region by sales, use region as the primary key and sales as the secondary key. The Range.Sort method supports up to three keys through its Key1, Key2, and Key3 arguments. Keep the header argument explicit for this kind of sort too.
For example, a range A1:D100 with headings in row 1 can be sorted first by region in column B, then by sales in column D, by passing B2 as Key1 and D2 as Key2. Set the corresponding order arguments to xlAscending or xlDescending to control each level. The range should still cover every column that belongs to each record.
For more complex sorts, such as several levels or a table-based sort, Excel’s SortFields collection is easier to extend than a long Range.Sort call. Clear existing sort fields before adding new ones; otherwise, old criteria can remain in the sort object and affect the result. Add the most important key first, followed by the tie-breaking keys.
Common header and range problems
- The heading gets sorted into the records: The range may have been sorted with
Header:=xlNo, or it may start below the heading. Confirm the range’s first row and set the header value to match. - The first record stays at the top: The range may start on the first data row while the code says
xlYes. Include the actual heading row, or usexlNofor a data-only range. - Values no longer match across columns: The macro likely sorted only the key column. Expand the sort range to include all columns in each record.
- New records are not included: A fixed range such as A1:D100 will not automatically grow beyond row 100. Use a last-row calculation, a table, or another method that matches how the data expands.
- The last row is detected incorrectly: A last-row calculation based on column A misses records where A is blank. Base it on a consistently populated column, and check that the chosen column actually represents the full data extent.
Another subtle issue is blank rows or columns inside a dataset. They can make a region-based range stop earlier than expected, while a fixed rectangular range can include extra empty cells. Inspect the actual range before sorting, especially if a macro uses CurrentRegion. That property expands around a cell until it reaches blank rows or columns, so it is useful for compact blocks but may not cover data separated by gaps.
VBA sorting compared with manual sorting
For a one-time sort, Excel’s ribbon is often the simplest option. Select a cell in the data, then use Data > Sort to open the Sort dialog and choose the column, sort order, and whether the data has headers. Excel may prompt you to expand the selection if you select only one column; choose the option that keeps the related rows together when the columns form complete records.
VBA is more useful when the same sort needs to happen repeatedly, when it is part of a larger workflow, or when a button or other macro should apply the same criteria consistently. It can also sort a table by a named column even if the table moves on the sheet. A macro is not automatically safer than a manual sort, though: an incorrectly defined range can consistently produce the wrong result.
Excel for the web can open workbooks that contain VBA, but it cannot create, run, or edit VBA macros. Use desktop Excel for Microsoft 365 on Windows or Mac to run the code. Macros are generally available in desktop Excel on Mac as well, but Windows-specific components and automation references may not work on Mac; ordinary worksheet range sorting is not usually dependent on those components.
Frequently Asked Questions
What does Header:=xlYes do in VBA?
It tells Excel that the first row of the range being sorted contains headings. Excel keeps that row out of the data sort. The range must include the actual header row for the setting to be correct.
Should I use xlYes or xlGuess?
Use xlYes when the range has a header and xlNo when it does not. These explicit choices are more predictable in a repeatable macro than xlGuess, which asks Excel to infer whether a header exists.
How do I sort a range with no header row?
Set Header:=xlNo and pass a range containing only records. Excel will include the first row of that range in the sort rather than treating it as a heading.
Can I keep the header while sorting by a different column?
Yes. Include the header row and all record columns in the sort range, set Header:=xlYes, and set the sort key to a cell in the column you want to use. The key column does not have to be the first column.
Can I undo a VBA sort with Ctrl+Z?
No. Running a VBA macro clears Excel’s regular undo history, so Ctrl+Z on Windows and Command+Z on Mac will not reverse the macro’s changes. Save a backup copy before running a sort that you may need to restore.
Related
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Is Nothing? Meaning, Checks, and Common FixesIn Excel VBA, a Range is Nothing when an object variable does not currently refer to a range…
- VBA AutoFilter Field 1: What It MeansIn Excel VBA, Field:=1 tells AutoFilter to filter the first column in the filter range—not necessarily worksheet column…
- Excel VBA IsError: Check for Worksheet Errors in CodeIn Excel VBA, IsError(expression) returns True when an expression evaluates to an error value and False otherwise. Use…
- VBA MsgBox: How to Display Messages in ExcelIn Excel VBA, MsgBox displays a dialog box containing a message and optional buttons, and it can return…
- VBA Find Position of a Substring in a StringIn VBA, use InStr to find a substring’s position within a string: it returns the position of the…
- VBA InputBox Options: Types, Choices, and ExamplesVBA’s basic InputBox accepts a prompt and returns what the user types as text; it does not provide…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.