How to sort multiple rows or columns independently at once in Excel?
In Excel, you can apply the Sort feature to sort only one row or column in a range of columns, but, if you need to sort multiple rows or columns independently at once to get the following screenshot shown. Are there any quick ways to solve this task in Excel?
Sort multiple columns independently at once with VBA code
Sort multiple rows independently at once with VBA code
Sort multiple columns independently at once with VBA code
To sort multiple columns independently in an ascending order, the following VBA code may help you, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Sort multiple columns independently at once:
Sub SortIndividualJR()
'Updateby Extendoffice
Dim xRg As Range
Dim yRg As Range
Dim ws As Worksheet
Set ws = ActiveSheet
On Error Resume Next
Set xRg = Application.InputBox(Prompt:="Range Selection:", _
Title:="Kutools for excel", Type:=8)
Application.ScreenUpdating = False
For Each yRg In xRg
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=yRg, Order:=xlAscending
.SetRange ws.Range(yRg, yRg.End(xlDown))
.Header = xlNo
.MatchCase = False
.Apply
End With
Next yRg
Application.ScreenUpdating = True
End Sub
3. Then press F5 key to run this code, and a prompt box is popped out to remind you select the data range that you want to sort based on each column, see screenshot:
4. And then click OK, each columns have been sorted dependently at once, see screenshot:
Sort multiple rows independently at once with VBA code
If you want to sort multiple rows independently, here also is a VBA code for you.
1. Select the data that you want to sort based on each rows.
2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Sort multiple rows independently at once:
Sub SortIndividualR()
'Updateby Extendoffice
Dim xRg As Range, yRg As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Set xRg = Selection
If xRg.Count = 1 Then
MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Application.ScreenUpdating = False
For Each yRg In xRg.Rows
yRg.Sort Key1:=yRg.Cells(1, 1), _
Order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortRows
Next yRg
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
Application.ScreenUpdating = True
End Sub
4. Then press F5 key to run this code, the data in each rows have been sorted at once, see screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
