To sort a selected range in Excel with VBA, use the range’s Sort method and specify a sort key, order, and header setting. Select one contiguous block and run the macro below to sort its rows in ascending order by the first column.
What “sort selection” means in VBA
A selection is the cell or range currently highlighted in the worksheet. Sorting that selection means Excel rearranges the rows or columns inside that range according to values in a chosen key column or row. For example, selecting A1:C20 and sorting by column A moves each row’s values in columns A, B, and C together.
This distinction matters: sorting only the highlighted cells in one column can separate those values from related data in neighboring columns. If names are in column A and sales amounts are in column B, select both columns before sorting by name or amount. A correctly defined range keeps each record intact.
In Excel for Microsoft 365 on Windows, the equivalent manual command is on the Data tab, in the Sort & Filter group. VBA is useful when you repeat the same sort, attach it to a button, or need a consistent multi-level sort. The macro does not change Excel’s sorting rules; it tells Excel which range, key, order, and header setting to use.
A basic macro for the selected range
Before running any macro, save a copy of the workbook. Sorting changes the order of worksheet data, and VBA actions cannot be reversed with Ctrl+Z. This example sorts the selected range by its first column, from smallest to largest or A to Z. It assumes the first row of the selection contains headers.
Sub SortSelectedRowsByFirstColumn()
Dim sortRange As Range
' Require a cell range rather than a chart or other object.
If TypeName(Selection) <> "Range" Then
MsgBox "Select a cell range first."
Exit Sub
End If
Set sortRange = Selection
' A multi-area selection cannot be treated as one rectangular block.
If sortRange.Areas.Count > 1 Then
MsgBox "Select one contiguous range."
Exit Sub
End If
If sortRange.Rows.Count < 2 Then
MsgBox "Select at least two rows to sort."
Exit Sub
End If
sortRange.Sort Key1:=sortRange.Columns(1), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom
End Sub
For a selection such as A1:C20, the key is the first selected column, A. The macro sorts complete rows within A1:C20, not the entire worksheet. xlAscending places text in alphabetical order and numbers in low-to-high order. To sort high-to-low or Z-to-A, change it to xlDescending. If the selection has no header row, change xlYes to xlNo; otherwise, Excel may treat the first data row as a label and leave it at the top.
How to add and run the macro
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if it needs to retain the VBA code. - In Excel for Windows, open the Developer tab and choose Visual Basic. If Developer is not visible, use File > Options > Customize Ribbon and enable it.
- In the Visual Basic Editor, choose Insert > Module, paste the code into the module, and close or switch away from the editor.
- Select the range on the worksheet, then run the macro from Developer > Macros. Choose
SortSelectedRowsByFirstColumnand click Run.
You can also run it from the editor with the insertion point inside the procedure and press F5. On Mac, Excel also includes VBA and the Visual Basic Editor, although keyboard shortcuts and the location of Developer-tab controls can vary with the keyboard and Excel release. Excel for the web does not run VBA macros; open the workbook in the desktop Excel app to use this code. Macro security settings may also require you to allow a workbook’s macros before they run. Only enable macros in files from sources you trust.
Choosing the sort key and direction
The key is the column or row whose values determine the order. In the basic example, sortRange.Columns(1) means the first column inside the selection, not necessarily worksheet column A. If you select D1:F30, that expression refers to column D. To sort that same selected block by its second column, use sortRange.Columns(2) as the key.
Excel sorts top to bottom by default in this example, which is the normal choice for lists where each record occupies a row. To sort a horizontal range from left to right, set Orientation:=xlLeftToRight and use a row within the selected range as the key. Direction and orientation are separate: ascending specifies the order of values, while orientation specifies whether Excel rearranges rows or columns.
For a simple one-key operation, the range’s Sort method is compact. When the task needs several sort levels, explicit sort fields make the priority easier to read. For instance, sort first by department in column A, then by surname in column B:
Sub SortSelectionByTwoColumns()
Dim sortRange As Range
Dim ws As Worksheet
If TypeName(Selection) <> "Range" Then Exit Sub
Set sortRange = Selection
If sortRange.Areas.Count > 1 Or sortRange.Columns.Count < 2 Then
MsgBox "Select one contiguous range with at least two columns."
Exit Sub
End If
Set ws = sortRange.Worksheet
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=sortRange.Columns(2), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
This sample sorts the first selected column as the primary key; the second column breaks ties. To reverse the order of either level, change that field’s Order to xlDescending. Both examples assume a header row, so adjust the header setting if your selection contains data only.
Headers, data types, and tables
The Header setting is not a cosmetic option. xlYes tells Excel that the top row of the selected range contains labels and should not be sorted with the records. xlNo means every row is data. If the selection includes a title line above the actual column headings, do not include that title in the sort range. Start with the header row and the records beneath it.
Mixed or inconsistent values can also produce surprising results. A column that looks numeric may contain numbers stored as text, blank cells, or labels such as “n/a.” Dates stored as text may not sort chronologically. Clean or standardize the key column if the resulting order appears inconsistent. Sorting also follows Excel’s treatment of blanks and text; it does not infer that every value that looks like a number or date has the corresponding data type.
Excel tables can be a safer choice for frequently updated lists because a table keeps its header row distinct and expands as records are added. You can still sort a table with VBA, but make sure the sort range covers the intended table data and that the key belongs to the table. For a one-off task, selecting the whole data block is often enough; for a reusable process, a table or a deliberately defined range reduces the risk of selecting too few rows.
Common mistakes and how to avoid them
- Sorting just one column: Related values can become misaligned. Select the full rectangular record range, or define that range explicitly in the macro.
- Using the wrong header setting: A header sorted as data can move into the list; a data row treated as a header will remain unsorted at the top. Choose
xlYesorxlNoto match the selection. - Including blank rows or unrelated content: A large selection may contain gaps or another table. Limit the range to the intended list rather than relying on a broad worksheet selection.
- Assuming the selection starts in column A:
Columns(1)refers to the first column of the selected range. If the selection starts in D, its first selected column is D. - Using a noncontiguous selection: Selections made with Ctrl-click can contain separate areas. The first macro detects this and stops because those areas do not form one sortable rectangle.
- Expecting Ctrl+Z to undo a macro: VBA actions generally clear Excel’s undo history. Save a copy first, and check the sorted range before continuing with other edits.
Another common source of confusion is a macro that sorts the wrong worksheet. A macro that uses Selection acts on the active selection, so the active workbook and sheet matter. If the procedure is attached to a button or run from another workbook, consider validating the worksheet or specifying a worksheet and range directly rather than depending on what happens to be selected.
Selection-based code versus a fixed range
Selection-based code is convenient when a person deliberately highlights a different list each time. Its trade-off is that the result depends on the current selection. A fixed-range macro is more predictable when the worksheet layout does not change. For example, replacing Set sortRange = Selection with Set sortRange = ThisWorkbook.Worksheets("Data").Range("A1:C20") targets a particular block. The worksheet name and address must match the workbook, and a fixed address needs updating if the list grows.
There is no single best approach for every workbook. Use a selection when users choose the data block, a named range or table when the data grows, and a fixed address for a stable, controlled layout. Avoid using CurrentRegion automatically without checking the sheet: it expands to adjacent nonblank cells and stops at blank rows or columns, which may not match the intended dataset.
When to use the Sort command instead
For an occasional sort, Excel’s built-in command is easier to inspect: select a cell in the dataset, choose Data > Sort, select the column and order, and confirm whether the data has headers. Selecting a cell within a well-formed table may let Excel identify the list automatically. Review the detected range before confirming, especially when other data sits nearby.
Use VBA when the same operation must be repeated, when it is part of a larger workflow, or when users need a single button for a carefully defined process. If all you need is a separate, dynamically sorted view and do not need to reorder the source data, Excel’s SORT worksheet function may be an alternative in supported Microsoft 365 or Excel 2021 versions. A formula-driven sorted result is not the same as sorting the original selection: it returns results elsewhere and does not rearrange the source cells.
Frequently Asked Questions
How do I sort a selected range in VBA?
Set a Range variable to Selection, then call its Sort method with a key, order, and header setting. Select the complete contiguous data block before running the macro.
How do I sort the selection by a different column?
Use the desired column within the selected range as the key. For example, sortRange.Columns(2) sorts by the second selected column, even if that is not worksheet column B.
What should I use for a range without headers?
Set Header:=xlNo in the simple example, or set .Header = xlNo in the multi-level example. With xlYes, Excel treats the first row as labels.
Can VBA sort a selection from largest to smallest?
Yes. Use xlDescending for the sort order. For dates, this places later dates first; for numbers, it places larger values first.
Can I run a VBA sort in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; the manual sort tools are available separately in the web app.
Related
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- UserForm VBA Controls: Types, Events, and ExamplesUserForm VBA controls are the buttons, text boxes, lists, labels, and other interface elements you place on a…
- Refresh Worksheet VBA: Methods, Examples, and FixesTo recalculate formulas on one worksheet with VBA, call that worksheet’s Calculate method. To refresh imported data, use…
- VBA Newline in MsgBox: Add Line BreaksTo add a newline in a VBA MsgBox, join the text with vbCrLf, for example: MsgBox "First line"…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- VBA Filter and Sort in Excel: Code and Key DetailsTo filter and sort a worksheet with VBA, use the AutoFilter method to show matching records and the…
- VBA AutoFilter Off: Remove Arrows or Clear FiltersTo show every row while keeping the filter controls, use VBA’s ShowAllData method when the worksheet is filtered.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.