Sort multiple rows Left-to-Right
I have a large excel file which I am trying to sort left-to-right by row, been trying to get a
vba approach to work, but my experience is too low. Surprising how hard it is to do what should be a simple task.
I have tried this code from another post but is mixing them and only 1st row gets arranged.
Sub sortfile22() Dim keyrange As String Dim DataRange As String keyrange = "A1:T1" DataRange = "A1:T8" Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(keyrange), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range(DataRange) .Header = xlYes .MatchCase = False .Orientation = xlLeftToRight .SortMethod = xlPinYin .Apply End With End Sub
My data looks something like this
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 48 1 16 40 75 21 50 3 35 73 80 53 33 46 38 2 69 54 63 79 54 27 62 56 79 67 71 75 28 35 78 66 60 65 5 47 31 38 68 21 56 77 43 9 64 80 72 16 17 46 10 22 63 34 41 8 53 60 6 79
You need to sort each row on its own. Therefore loop row wise through the
DataRange and sort each
DataRow on its own.
Option Explicit Public Sub SortRowWise() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") Dim DataRange As Range Set DataRange = ws.Range("A1").CurrentRegion Dim DataRow As Range For Each DataRow In DataRange.Rows 'loop through all rows of the data With ws.Sort .SortFields.Clear .SortFields.Add Key:=DataRow, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Rng:=DataRow .Header = xlNo .MatchCase = False .Orientation = xlLeftToRight .SortMethod = xlPinYin .Apply End With Next DataRow End Sub
So this input data …
… will sort as:
Sort multiple rows Left-to-Right, A pop-up will appear: Click on "Options" at the bottom. Actually the built in sort algorithm can sort left to right .Orientation = xlLeftToRight and should be faster because it can use multi-threading while VBA cannot. Actually because of that built in functions should always be faster than self written VBA code. • And you should change to Long Excel has more rows than Integer can handle.
Was actually a bit harder than I expected, but here's the code:
I also borrowed, the standard vba quicksort algorithm, courtesy of wellsr.com, but feel free to use any other sorting algorithm you want, just make sure you change this line of code:
Quicksort sortrow, LBound(sortrow), UBound(sortrow) ' calling QuickSort
Then the code is executed the following way:
Private Sub main() Dim i As Integer, lc As Long, lr as Long, j As Integer Dim arr As Variant lr = Cells(Rows.Count, 1).End(xlUp).Row ' finds the last row lc = Cells(1, Columns.Count).End(xlToLeft).Column ' finds the last i-th column arr = Range(Cells(1, 1), Cells(lr, lc)).Value2 Dim sortrow() As Integer ' sorting each row separately For i = LBound(arr, 1) To UBound(arr, 1) ' for every row For j = LBound(arr, 2) To UBound(arr, 2) ' add ReDim Preserve sortrow(1 To j) sortrow(j) = arr(i, j) ' adding arr elements to SortRow Next j Quicksort sortrow, LBound(sortrow), UBound(sortrow) ' calling QuickSort For j = LBound(sortrow) To UBound(sortrow) Cells(i, j) = sortrow(j) ' print the sorted results Next j Next i End Sub
Works as expected:
How to sort multiple columns from left to right in Excel?, You need to sort each row on its own. Therefore loop row wise through the DataRange and sort each DataRow on its own. Option Explicit� Changing Orientation of Sorting. Follow the below steps to sort the data from left to right: Step 1: Click any cell on the data table. Step 2: Go to the “Data” tab. In the “Data & Filter” group, select the “Sort” button as shown in the screenshot below :
Slightly modified array approach
Just for the sake of the art I modified @Rawrplus' valid & fast solution by reducing three loops to one and by avoiding permanent redimming.
Option Explicit ' declaration head of code module Private Sub Main() With Sheet1 ' << reference sheet via code name, e.g. Sheet1 '  do some statistics over data range Dim i&, lr&, lc& ' declare datatype Long lr = .Cells(.Rows.Count, 1).End(xlUp).Row ' find last row lc = .Cells(1, .Columns.Count).End(xlToLeft).Column ' find last column '  assign data to array Dim arr(), sortrow() ' declare Variant arrays arr = .Range(.Cells(1, 1), .Cells(lr, lc)).Value2 ' assign range data to 1-based 2-dim datafield array '  sort row data and write them back to sheet For i = LBound(arr, 1) To UBound(arr, 1) ' loop through row data sortrow = Application.Index(arr, i, 0) ' assign current row data to 1-dim sortrow array Quicksort sortrow, LBound(sortrow), UBound(sortrow) ' calling QuickSort .Cells(i, 1).Resize(1, UBound(sortrow)) = sortrow ' write sorted row data back to sheet Next i End With End Sub
[SOLVED] How to sort multiple rows from left to right?, Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/ Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum. Split Tools:� How to sort multiple rows from left to right? I have a table which has 10 columns, a = name, b-e = Address 1,2,3,4, f-j = date of call 1,2,3,4,5. Ideally column f should show the date of the latest call, going down the row to j from newest to oldest.
Sort Lottery Number Rows in Excel, Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not� Sort left to right in Excel – instructions Select your data. But exclude headers (ie first column) Now go to Home > Sort & Filter > Custom sort Click on “options” Now select “Sort left to right”, click OK. Now you can set sorting by row, rather than column. Specify row number you want to sort on and
Sort Multiple Rows with a Macro. If you don't want to mess with formulas, you could use a macro to sort each row, left to right. This macro, from� Click the Options button, then in the Sort Options dialog box select Sort left to right, and click OK. In the Sort dialog box, click the Sort by drop-down arrow and select Row 1. Make sure the Sort On selection is Values and Order selection is A to Z, then click OK. The data is sorted left to right by using the first column.
Step 2: Click Data tab in the ribbon above and i the Sort and Filter group click Sort button. Step 3: From the dialogue box click options button and from second dialogue click Sort left to right orientation. Click ok. Doing this now Sort will now work on row basis horizontally from left to right as opposed to sorting on column basis from top to
- You might benefit from reading How to avoid using Select in Excel VBA.
- Actually the built in sort algorithm can sort left to right
.Orientation = xlLeftToRightand should be faster because it can use multi-threading while VBA cannot. Actually because of that built in functions should always be faster than self written VBA code. • And you should change to
LongExcel has more rows than
- I'm not arguing efficiency here, just an alternative option. Took me quite some time to get it right so might as well post it. As to the
Long, yeah you are right, though I doubt OP will exceed
Integerrange given his data sats, but for sake of correctness I have edited it
- You could recode the loop after
QuickSortas simple one liner:
Cells(i, 1).Resize(1, UBound(sortrow)) = sortrow. BTW suggest to fully qualify range references :-) @Rawrplus `
- Additional hint: furthermore you could even replace the
sortrowdeclaration once by
ReDim sortrow(1 To lc) As Integeravoiding permanent redimming.
- @T.M. thanks for the input, I'll look into it tomorrow.