VBA & Macro Problems

Sort Every Row in an Excel Range with VBA

Anum MushtaqAnum Mushtaq Nov 20, 2026 868 views

Question details

Sort the values in each row of an Excel range independently from left to right using VBA.

Sort Every Row in an Excel Range with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to sort each row in a range such as B2:K400 without manually sorting hundreds of rows.
Observed behavior
Each row's values should be sorted independently across the selected columns.
Before you start

Identify the worksheet and range to sort, and exclude any header rows. Sorting each row independently changes which values appear under each column heading, so confirm that the columns do not need to remain paired.

Solution 1Recommended

Loop through the range and sort each row

Use a VBA macro to sort each row from left to right in ascending order.

1
Open the VBA editor

Open the workbook in desktop Excel, then choose Developer > Visual Basic. In the editor, select Insert > Module.

2
Add the macro

Paste this code into the module: Sub SortEachRow() Dim rowRange As Range Dim wasUpdating As Boolean wasUpdating = Application.ScreenUpdating On Error GoTo HandleError Application.ScreenUpdating = False For Each rowRange In ActiveSheet.Range("B2:K400").Rows rowRange.Sort Key1:=rowRange.Cells(1, 1), _ Order1:=xlAscending, Header:=xlNo, _ Orientation:=xlSortRows Next rowRange CleanUp: Application.ScreenUpdating = wasUpdating Exit Sub HandleError: MsgBox "Sort failed: " & Err.Description, vbExclamation Resume CleanUp End Sub

3
Set the range

Change B2:K400 in the code to the range you want to sort. Each iteration passes one row, such as B2:K2 and then B3:K3, to the Sort method.

4
Run the macro

Return to Excel and select the target worksheet. Choose Developer > Macros, select SortEachRow, and click Run. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro.

Loop through the range and sort each row
How the sort works: Orientation:=xlSortRows sorts from left to right, and Header:=xlNo treats every cell in each selected row as data. The macro restores the previous ScreenUpdating setting when it finishes or encounters an error.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Excel format compatibility makes it easy to move existing files between office suites; Excel-specific VBA macros may require Microsoft Excel.

Review and edit spreadsheet ranges in WPS SpreadsheetsOpen and edit Microsoft Excel formats such as XLSX and XLSUse a familiar spreadsheet interface for everyday tasksMove existing spreadsheet files to a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I sort each row in descending order?

In the macro, replace Order1:=xlAscending with Order1:=xlDescending, then run SortEachRow again from Developer > Macros.

Does the macro sort the whole range as one block?

No. The For Each loop processes each row separately. For B2:K400, it sorts B2:K2, then B3:K3, and continues through B400:K400.

How can I sort a different set of rows or columns?

In the VBA editor, change ActiveSheet.Range("B2:K400") to the desired range. Select the correct worksheet in Excel before running the macro.

Why can't I find the macro after reopening the workbook?

Save the file through File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). When reopening it, enable macros only if you trust the workbook and its source.