The quickest way to sort data with an Excel VBA macro is to record a sort and then edit the generated code, or to write a short macro using the Range.Sort method. For example, to sort cells A2:A20 by the values in column A, you can use: Range("A2:A20").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo. Below is a complete guide to writing, customizing, and troubleshooting VBA sort macros.
Why Use a VBA Macro to Sort Data?
Excel’s built-in sort (Data tab > Sort) works fine for one-time sorting. But a macro becomes valuable when you sort the same data repeatedly, need to sort multiple sheets in one go, or want to automate sorting as part of a larger workflow (e.g., after importing data). A VBA macro can also apply a consistent sort order every time, reducing the risk of human error.
How the Range.Sort Method Works
The Sort method is applied to a range object. The essential parameters are:
Key1– the column (or row) that determines the sort order.Order1–xlAscending(A to Z, smallest to largest) orxlDescending(Z to A, largest to smallest).Header–xlYesif the first row contains headers,xlNoif not.
You can also specify a second sort key with Key2 and Order2, and a third with Key3. The Sort method sorts the entire range you specify, so if you include all columns in the range, they move together.
Writing a Simple Sort Macro
Here’s a complete macro that sorts a table with headers (A1:D20) by column B (e.g., a “Last Name” column) in ascending order:
Sub SortByColumnB()
' Sorts A1:D20 by column B, assuming row 1 has headers
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("A1:D20").Sort _
Key1:=ws.Range("B2"), _
Order1:=xlAscending, _
Header:=xlYes
End Sub
To run this, press Alt+F8 (on Windows) or Option+F8 (on Mac), select SortByColumnB, and click Run. Always save a copy of your workbook before running any macro, because macros cannot be undone with Ctrl+Z.
Recording a Sort Macro: The Easiest Way
If you’re not comfortable writing code, use the macro recorder:
- Select the data range (including headers).
- Go to the View tab > Macros > Record Macro.
- Give the macro a name (e.g.,
SortData) and click OK. - Go to the Data tab > Sort, set your sort criteria, and click OK.
- Go back to View > Macros > Stop Recording.
The recorded code will use the Sort method with your exact settings. You can then open the VBA editor (Alt+F11) to view and modify it. Note that the recorder often uses SortFields and Apply—this is just a longer way of doing the same thing.
Sorting by Multiple Columns
To sort by more than one column, add Key2 and Key3. For example, to sort by Last Name (column B) and then by First Name (column A):
Sub SortByTwoColumns()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("A1:D20").Sort _
Key1:=ws.Range("B2"), Order1:=xlAscending, _
Key2:=ws.Range("A2"), Order2:=xlAscending, _
Header:=xlYes
End Sub
Remember that the range must include all columns you want to move together. If you only sort a single column, the rest of the data will not reorder—a common mistake.
Common Problems and How to Fix Them
- Data not sorting correctly: Make sure the range includes all columns and that the header row is correctly identified. If your data has no headers, set
Header:=xlNo. - Sorting only one column: If you select only a single column in the
Sortmethod, only that column is sorted. Always select the full data range. - Numbers sort as text: If numbers are stored as text, they will sort alphabetically (e.g., 10 before 2). Convert them to numbers using the
Valueproperty or the Text to Columns feature. - Macro runs but nothing happens: Check that the worksheet name in the code matches your actual sheet name. Also, ensure the range references are correct.
- Error “Sort reference is not valid”: This often occurs when the
Key1range is not within the range being sorted. Ensure the key cell is inside the sort range.
Sorting a Whole Sheet or a Dynamic Range
To sort an entire sheet (all used rows and columns), you can use ws.UsedRange. For a range that changes size, use CurrentRegion if the data is contiguous:
Sub SortDynamicRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Assumes A1 is the top-left cell of a contiguous data block
ws.Range("A1").CurrentRegion.Sort _
Key1:=ws.Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes
End Sub
CurrentRegion expands to include all adjacent cells with data, so it will automatically include new rows or columns as long as there are no blank rows or columns within the data.
Frequently Asked Questions
Can I sort by row instead of column?
Yes. The Sort method has an Orientation parameter. Use Orientation:=xlSortRows to sort left-to-right instead of top-to-bottom. For example, to sort rows 1 to 5 by the values in row 1, you’d write Range("A1:E5").Sort Key1:=Range("B1"), Orientation:=xlSortRows.
How do I sort with a custom order (e.g., High, Medium, Low)?
Use the CustomOrder parameter. For example: Sort Key1:=Range("A2"), Order1:=xlAscending, CustomOrder:="High,Medium,Low". The custom list must be comma-separated and match the cell values exactly.
Why does my macro sort only the selected column?
Because you likely specified a single-column range in the Sort method. Always include the entire data range (all columns) in the range you are sorting, even if the key is only one column.
Can I undo a sort done by a macro?
No. Macros cannot be undone with Ctrl+Z. That’s why it’s critical to save a backup copy of your workbook before running any sorting macro. You can also add a line at the start of the macro to save a copy automatically, but that’s beyond the basics.
Does the macro work on Excel for Mac?
Yes, VBA is supported on Excel for Mac. The code is identical, but the keyboard shortcuts differ (e.g., Alt+F11 is Option+F11). Be aware that some advanced features may behave slightly differently, but the Sort method works the same.
Alternatives to VBA for Sorting
If you don’t want to use VBA, consider these built-in alternatives:
- Sort buttons: Select a cell in the column and click the A→Z or Z→A button on the Data tab.
- Sort dialog: Data > Sort gives you multiple levels and custom orders.
- SORT function: In Excel 365,
=SORT(array, [sort_index], [sort_order], [by_col])returns a sorted array without changing the original data. This is a formula-based approach that updates automatically.
VBA is best when you need to automate sorting as part of a larger task, such as cleaning a report every morning. For one-off sorting, the built-in tools are simpler and safer.
Related
- Excel Template Sales Tracker: How to Build One
- Excel Template to Pay Off Debt? Yes, Here’s How
- Excel Inventory Template: How to Track Stock
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Format Cell Borders: A Complete GuideTo format cell borders in Excel VBA, use the Borders property of a Range object, setting properties like…
- Excel VBA Select Case: Syntax and ExamplesIn Excel VBA, Select Case runs one block of code based on the value of an expression, making…
- Excel VBA Exit Function: When and How to Use It SafelyIn Excel VBA, Exit Function stops the current function immediately and returns control to the statement that called…
- Excel UserForm: How to Show It with VBATo show an Excel UserForm, create the form in the Visual Basic Editor, then run a VBA procedure…
- Excel VBA: Protect a Sheet and Allow Filter and SortYes. In Excel VBA, protect the worksheet with AllowFiltering:=True and AllowSorting:=True. The filter controls must already exist, and…
- Excel VBA Loop Through a Folder: Practical ExamplesTo loop through files in a folder with Excel VBA, use the Dir function to find a matching…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.