Sort left to right rows

[harky]

I had thousand of data which need to Sort left to right rows in numerical order but except s/n

here is the sample data base. (but the row contain data can be as many as this)

is there any fast way to do it?? mil thanks.

[TABLE="width: 500"]
<tbody>[TR]
[TD]s/n[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1112[/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD]434[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1114[/TD]
[TD]34[/TD]
[TD][/TD]
[TD]555[/TD]
[TD]555[/TD]
[TD]545[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1116[/TD]
[TD]642[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]111[/TD]
[/TR]
</tbody>[/TABLE]


I got this code from https://www.mrexcel.com/forum/excel-questions/693675-rearrange-columns-w-visual-basic-applications-using-row-sort-order.html , it look well but how to excluded the first col?

Sub SortLtoR()    
    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = ActiveSheet
    Set rng = ws.UsedRange
    
    With ws.Sort
        With .SortFields
            .Clear
            .Add rng.Rows(1)
        End With
        
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        
        .Apply
    End With
End Sub[COLOR=#3E3E3E]
[/COLOR]

 

[GTO]

Re: [NEED HELP] Sort left to right rows

Greetings Harky,

I may be misreading, but if you are simply asking how to change the sort from vertical to horizontal, select the range you want sorted, select Sort and Filter|Custom Sort, and in the dialog, click on Options and change to Left to Right.

Hope that helps,

Mark

 

[harky]

Ya
But I can't do it 1by1 as I have thousands row to be sort.

The above script works but I will like to exclude
A column and row for header and s/n

Greetings Harky,

I may be misreading, but if you are simply asking how to change the sort from vertical to horizontal, select the range you want sorted, select Sort and Filter|Custom Sort, and in the dialog, click on Options and change to Left to Right.

Hope that helps,

Mark

 

[GTO]

I realize you just stated that you cannot do it 1 by 1, but are you actually looking to sort each row individually, albeit by macro? Would the expected results of you sample data in Post #1 be like the below?

Excel Workbook
	A	B	C	D	E
1	s/n
2	1111	1
3	1112	23	434
4	1113
5	1114	34	545	555	555
6	1115
7	1116	3	9	111	642
Sheet2

Excel 2010

 

[harky]

Yes, to be done using macro.
.. But excluded the row 1 and col A for header and serial.

Hehe... Thanks.

I realize you just stated that you cannot do it 1 by 1, but are you actually looking to sort each row individually, albeit by macro? Would the expected results of you sample data in Post #1 be like the below?

Excel Workbook
	A	B	C	D	E
1	s/n
2	1111	1
3	1112	23	434
4	1113
5	1114	34	545	555	555
6	1115
7	1116	3	9	111	642
Sheet2

Excel 2010

 

[GTO]

Presuming "S/N" is in cell A1:

Before:

Excel Workbook
	A	B	C	D	E	F	G	H
1	S/M
2	1111	8846	62	7688	3627	2376	6100	7327
3	1112	3086		4408		1742	8991
4	1113	6231					8783	3663
5	1114	4385
6	1115	6385	4340	7264	5391	3664	9705	8592
7	1116
8	1117	3438		404	8346	2046	6934	1023
9	1118	3277	7595	6314	4464	1546	9917	1794
10	1119		3124	8722	6554	3374	9603	1234
Sheet4 (2)

Excel 2010

After:

Excel Workbook
	A	B	C	D	E	F	G	H
1	S/M
2	1111	62	2376	3627	6100	7327	7688	8846
3	1112	1742	3086	4408	8991
4	1113	3663	6231	8783
5	1114	4385
6	1115	3664	4340	5391	6385	7264	8592	9705
7	1116
8	1117	404	1023	2046	3438	6934	8346
9	1118	1546	1794	3277	4464	6314	7595	9917
10	1119	1234	3124	3374	6554	8722	9603
Sheet4 (2)

Excel 2010

In a junk copy of your workbook, try...

In a Standard Module:

Option Explicit
  
Sub example()
Dim WKS       As Worksheet
Dim rng2Sort  As Range
Dim Index     As Long
  
  Set WKS = ActiveSheet ' or by sheet's tab name ThisWorkbook.Worksheets("Sheet4") or just use Codename
  Set rng2Sort = WKS.Range("A1").CurrentRegion
  Set rng2Sort = rng2Sort.Offset(1, 1).Resize(rng2Sort.Rows.Count - 1, rng2Sort.Columns.Count - 1)
  
  For Index = rng2Sort.Rows.Count To 1 Step -1
    With rng2Sort.Rows(Index)
      .Sort Key1:=.Cells(1), Order1:=xlAscending, Orientation:=xlSortRows
    End With
  Next
  
End Sub

Hope that helps,

Mark

 

[GTO]

Ergh... I remembered you said that there could be thousands of rows. I'd tack in killing repaints and any calculations to speed it up...

Option Explicit
  
Sub example()
Dim WKS       As Worksheet
Dim rng2Sort  As Range
Dim Index     As Long
Dim CalcMode  As XlCalculation
  
  CalcMode = Application.Calculation
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  Set WKS = ActiveSheet ' or by sheet's tab name ThisWorkbook.Worksheets("Sheet4") or just use Codename
  Set rng2Sort = WKS.Range("A1").CurrentRegion
  Set rng2Sort = rng2Sort.Offset(1, 1).Resize(rng2Sort.Rows.Count - 1, rng2Sort.Columns.Count - 1)
  
  For Index = rng2Sort.Rows.Count To 1 Step -1
    With rng2Sort.Rows(Index)
      .Sort Key1:=.Cells(1), Order1:=xlAscending, Orientation:=xlSortRows
    End With
  Next
  
  Application.Calculation = CalcMode
  Application.ScreenUpdating = True
  
End Sub

 

[harky]

OMG, it work pretty well!!!
U save me.
it could have spent hour just to sort that

Ergh... I remembered you said that there could be thousands of rows. I'd tack in killing repaints and any calculations to speed it up...

Option Explicit
  
Sub example()
Dim WKS       As Worksheet
Dim rng2Sort  As Range
Dim Index     As Long
Dim CalcMode  As XlCalculation
  
  CalcMode = Application.Calculation
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  Set WKS = ActiveSheet ' or by sheet's tab name ThisWorkbook.Worksheets("Sheet4") or just use Codename
  Set rng2Sort = WKS.Range("A1").CurrentRegion
  Set rng2Sort = rng2Sort.Offset(1, 1).Resize(rng2Sort.Rows.Count - 1, rng2Sort.Columns.Count - 1)
  
  For Index = rng2Sort.Rows.Count To 1 Step -1
    With rng2Sort.Rows(Index)
      .Sort Key1:=.Cells(1), Order1:=xlAscending, Orientation:=xlSortRows
    End With
  Next
  
  Application.Calculation = CalcMode
  Application.ScreenUpdating = True
  
End Sub

 

[harky]

This will works for Excel 2010 and above?

 

[GTO]

Whilst I cannot swear to above 2010, it should.