Yes. In Excel for Microsoft 365 on Windows, VBA can sort a range or table by one or more columns using Excel’s built-in sort features. A reliable macro identifies the full data range, specifies whether headers are present, and sets each sort key and direction before applying the sort.
What VBA sorting does
A VBA sort rearranges rows in a worksheet according to values in one or more columns. If a row contains a customer, region, and sales total, sorting by the region should move all three values together; sorting only the region cells would separate the data and corrupt the records. The central rule is to sort the complete rectangular data range, not just the column used as the key.
Excel provides two common approaches. The Range.Sort method is compact and useful for straightforward sorts. The worksheet’s Sort object, configured through SortFields, makes multiple sort levels and options easier to read and maintain. The example below uses the Sort object, which is a good pattern when you expect the macro to grow.
VBA is useful when a sort must be repeated, triggered by a button, or included in a larger workflow such as importing and cleaning data. It is not necessary for a one-off sort: the standard Sort controls on Excel’s Data tab are often quicker and easier to adjust interactively.
Prepare the data before writing a macro
Decide what counts as the dataset and what each sort level should do before coding. For example, imagine a worksheet named Sales with headers in row 1 and records in columns A through C: Customer, Region, and Sales. You might sort by Region alphabetically, then by Sales from largest to smallest within each region.
- Keep one field per column and one record per row. Avoid blank rows or columns inside the dataset, because they can make automatic range detection stop too early.
- Use a clear header row and tell VBA that the range has headers. Otherwise, a header such as “Region” may be sorted into the records.
- Choose a dependable way to find the last record. The example uses the last occupied cell in column A, so that column should be populated for every record.
- Check whether cells contain actual values or inconsistent text. Numbers stored as text, stray spaces, and mixed date formats can produce a sort order that looks unexpected.
If your data starts in a different row, spans different columns, or has a column that may be blank for some records, adjust the range-finding logic to match the worksheet. A macro cannot reliably infer the intended dataset from ambiguous layout.
A complete VBA example for sorting data
This macro sorts the Sales sheet’s A:C data by Region ascending and then Sales descending. It assumes row 1 contains headers, column A is filled for every record, and there is at least one record below the header. It leaves the header in place and moves each whole row together.
Sub SortSalesByRegionAndAmount()
Dim ws As Worksheet
Dim lastRow As Long
Dim sortRange As Range
Dim regionKey As Range
Dim salesKey As Range
' Work with the worksheet in this workbook.
Set ws = ThisWorkbook.Worksheets("Sales")
' Find the last record using the populated Customer column.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "There are no sales records to sort."
Exit Sub
End If
' Include every field in each record, not only the sort keys.
Set sortRange = ws.Range("A1:C" & lastRow)
Set regionKey = ws.Range("B2:B" & lastRow)
Set salesKey = ws.Range("C2:C" & lastRow)
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=regionKey, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=salesKey, SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
To use it on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and change the sheet name or column references if your layout differs. Run it from the editor, or use Developer > Macros in Excel. If the Developer tab is missing on Windows, enable it through File > Options > Customize Ribbon.
Excel for Mac also supports VBA macros in the desktop application, although available keyboard shortcuts can depend on the keyboard and macOS settings; use the ribbon’s Developer and Macros controls if a shortcut differs. Excel for the web cannot create, run, or edit VBA macros. Open the workbook in desktop Excel to run this code.
How sort keys and sort order work
A sort key is the range of cells whose values determine row order. In the example, B2:B(last row) is the first key, so Region is the primary sort. C2:C(last row) is the second key, so Sales determines the order only among rows whose Region values match. The order in which SortFields are added is the priority order: add the most important key first.
Use xlAscending for A-to-Z, smallest-to-largest, or oldest-to-newest ordering, and xlDescending for the reverse. The meaning depends on the data type. Excel sorts numbers numerically when they are stored as numbers, but text that looks numeric may sort alphabetically instead. For example, text values “100” and “20” can appear in an order different from the numerical order of 100 and 20.
The example uses xlYes to mark the first row as headers. Use xlNo only when the range has no header row. The MatchCase setting controls whether text sorting distinguishes uppercase and lowercase; it is False in this example. Orientation set to xlTopToBottom means records are arranged vertically by columns, the usual layout for a worksheet table.
Finding the right range as data grows
A macro with a fixed range such as A1:C100 stops including records once the dataset grows past row 100. The example instead finds the last non-empty cell in column A using End(xlUp), then builds the sort range to that row. This is simple and effective when column A is consistently filled.
There are limitations to that method. If a record has a blank in column A, the last-row calculation can miss data below the blank only if the bottom-most records also leave the anchor column empty. If the worksheet contains notes or unrelated content below the table in column A, the calculation can include those rows. Choose an anchor column that is reliably populated and keep unrelated content outside the data area.
For a data range with no blank rows or columns, CurrentRegion can also identify a contiguous block around a cell. It may not be appropriate when the worksheet has intentional gaps or nearby notes, because those boundaries affect what Excel considers part of the region. For recurring workflows, an Excel Table is often a stronger choice: tables expand as rows are added and have explicit headers. A table can also be sorted using its ListObject.Sort object, with the table itself as the scope.
Common sorting problems and how to prevent them
- Only one column moves: The sort range probably covers the key column but not the rest of the record. Set the sort range to include every related field.
- The header moves into the data: Set the Header property correctly. Use xlYes for a header row and xlNo for data without headers.
- Some new rows are not sorted: Replace a hard-coded final row with logic that finds the current data boundary, or use a table that expands with new entries.
- Numbers or dates appear out of order: Check for numbers stored as text, inconsistent date values, extra spaces, or mixed formats. Correcting the source values is usually better than trying to compensate in the sort settings.
- A repeated run gives a surprising result: Confirm that SortFields are cleared before new fields are added. Otherwise, old sort levels may remain and affect the result.
- The macro reports an error about a worksheet: Check that the worksheet name in the code exactly matches the tab name and that the named sheet is in the workbook referenced by ThisWorkbook.
Another easily missed issue is that a sort changes row positions. Any formulas or references that depend on a particular row number may show different records afterward, even when Excel adjusts ordinary cell references. Think about downstream reports, formulas, and other macros before automating a sort.
VBA sorting compared with Excel’s built-in sort
For a manual task, select a cell in the dataset and use Data > Sort to choose sort columns and order. Excel’s interface makes it easier to inspect levels, change options, and catch an incorrect selection before applying the sort. If the data is formatted as a table, its header dropdowns also provide quick sorting by an individual field.
VBA is better when the same operation must be repeated consistently or combined with other steps. It can encode the exact range, header setting, and sort priority so a user does not need to rebuild those choices each time. The trade-off is that the macro depends on assumptions about sheet names and data layout. When those assumptions change, the code may need updating.
For supported Microsoft 365 versions, worksheet functions such as SORT can return a sorted result in cells without moving the original data. That can be useful when a live sorted view is wanted, while a VBA sort rearranges the source rows themselves. Choose based on whether the goal is to change the original order, create a separate view, or repeat a workflow automatically.
Macro safety and workbook handling
Save a copy of the workbook before running a sorting macro. Macro actions generally cannot be reversed with Ctrl+Z, so a backup gives you a way to restore the original arrangement if the range or sort keys were wrong. Confirm the code’s worksheet and range before running it on important data.
Save a workbook containing VBA in a macro-enabled format such as .xlsm; a standard .xlsx file does not preserve VBA code. Only enable macros in workbooks from sources you trust. Excel may block macros from files downloaded from the internet or apply organizational security settings, and those protections should not be bypassed casually. A macro does not need internet access to sort local worksheet data.
Frequently Asked Questions
How do I sort a column in Excel using VBA?
Set a sort range that includes the full records, specify the column as a sort key, choose ascending or descending order, and set the header option. Sorting only the key column can detach values from the rest of each row.
How do I sort by two columns in VBA?
Add two SortFields in priority order before applying the sort. The first is the primary key, and the second breaks ties within matching values in the first key. Set the order separately for each field.
Can VBA sort data when new rows are added?
Yes, if the macro calculates the current last row or sorts an expanding Excel Table. The example finds the last record from column A, so that column must be populated consistently and the macro’s range must match the actual table layout.
Why does my VBA sort move the header row?
The sort’s Header setting may be set to xlNo even though the selected range includes headers. Set it to xlYes when the first row contains field names, and make sure the sort range begins with that header row.
Can I run a VBA sort in Excel for the web?
No. Excel for the web can open a workbook that contains macros, but it cannot run or edit VBA. Use the Excel desktop application for Windows or Mac to work with VBA.
Can I undo a VBA sort with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a separate copy before running a sorting procedure, especially when preserving the original row order matters.
Related
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- MS Access VBA Error Handling: Guide, Patterns, and TipsIn Microsoft Access VBA, handle runtime errors with an On Error statement: use On Error GoTo HandlerLabel for…
- VBA Array of Arrays: How Nested Arrays WorkYes. VBA can store an array inside an element of a Variant array, creating a jagged or “array…
- Excel Dashboard VBA: Build and Refresh a Useful DashboardExcel dashboard VBA is Visual Basic for Applications code that automates tasks such as refreshing data, updating PivotTables,…
- VBA Double Loop: How Nested For Loops Work in ExcelA VBA double loop is a loop inside another loop: the outer loop runs once, and for each…
- VBA MsgBox Width: What You Can and Can’t ControlVBA’s MsgBox has no width argument or width property, so you cannot set its exact dimensions directly. Its…
- Excel UserForm Controls: A Practical Guide to Types and UseExcel UserForm controls are the buttons, text boxes, lists, and other interface elements you add to a VBA…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.