Hi!
Below i had a Macro (which i got help from someone in other forum) and it work pretty well to what i want BUT i appreciate if someone could help me improve
Could is possible to sort row base on pre-sorted list?
the pre-sorted list is name as "çustomlist" sheet
The code is Left to Right, Excluded the row 1 and col A for header and serial.
"S/N" is in cell A1:
Before:
Sheet4 (2) [TABLE="border: 1"]
A B C D E F G H
1
S/M
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr]
2 [TD="align: right"]1111[/TD]
[TD="align: right"]8846[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]7688e[/TD]
[TD="align: right"]7688[/TD]
[TD="align: right"]2376[/TD]
[TD="align: right"]6100[/TD]
[TD="align: right"]6100a[/TD]
3 [TD="align: right"]1112[/TD]
[TD="align: right"]3086[/TD]
[TD="align: right"]3086a[/TD]
[TD="align: right"]ABC[/TD]
[TD="align: right"]8991[/TD]
4 [TD="align: right"]1113[/TD]
[TD="align: right"]6231[/TD]
[TD="align: right"]8783[/TD]
[TD="align: right"]3663[/TD]
5 [TD="align: right"]1114[/TD]
[TD="align: right"]4385[/TD]
6 [TD="align: right"]1115[/TD]
[TD="align: right"]6385[/TD]
[TD="align: right"]4340[/TD]
[TD="align: right"]7264[/TD]
[TD="align: right"]5391[/TD]
[TD="align: right"]3664[/TD]
[TD="align: right"]9705[/TD]
[TD="align: right"]8592[/TD]
7 [TD="align: right"]1116[/TD]
8 [TD="align: right"]1117[/TD]
[TD="align: right"]3438[/TD]
[TD="align: right"]404[/TD]
[TD="align: right"]8346[/TD]
[TD="align: right"]2046[/TD]
[TD="align: right"]6934[/TD]
[TD="align: right"]1023[/TD]
9 [TD="align: right"]1118[/TD]
[TD="align: right"]3277[/TD]
[TD="align: right"]7595[/TD]
[TD="align: right"]6314[/TD]
[TD="align: right"]4464[/TD]
[TD="align: right"]1546[/TD]
[TD="align: right"]9917[/TD]
[TD="align: right"]1794[/TD]
10 [TD="align: right"]1119[/TD]
[TD="align: right"]3124[/TD]
[TD="align: right"]8722[/TD]
[TD="align: right"]6554[/TD]
[TD="align: right"]3374[/TD]
[TD="align: right"]9603[/TD]
[TD="align: right"]1234[/TD]
[/TABLE]
After:
Sheet4 (2) [TABLE="border: 1"]
A B C D E F G H
1
S/M
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr]
2 [TD="align: right"]1111[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]2376[/TD]
[TD="align: right"]6100[/TD]
[TD="align: right"]6100a[/TD]
[TD="align: right"]7688[/TD]
[TD="align: right"]7688e[/TD]
[TD="align: right"]8846[/TD]
3 [TD="align: right"]1112[/TD]
[TD="align: right"]ABC[/TD]
[TD="align: right"]3086[/TD]
[TD="align: right"]3086a[/TD]
[TD="align: right"]8991[/TD]
4 [TD="align: right"]1113[/TD]
[TD="align: right"]3663[/TD]
[TD="align: right"]6231[/TD]
[TD="align: right"]8783[/TD]
5 [TD="align: right"]1114[/TD]
[TD="align: right"]4385[/TD]
6 [TD="align: right"]1115[/TD]
[TD="align: right"]3664[/TD]
[TD="align: right"]4340[/TD]
[TD="align: right"]5391[/TD]
[TD="align: right"]6385[/TD]
[TD="align: right"]7264[/TD]
[TD="align: right"]8592[/TD]
[TD="align: right"]9705[/TD]
7 [TD="align: right"]1116[/TD]
8 [TD="align: right"]1117[/TD]
[TD="align: right"]404[/TD]
[TD="align: right"]1023[/TD]
[TD="align: right"]2046[/TD]
[TD="align: right"]3438[/TD]
[TD="align: right"]6934[/TD]
[TD="align: right"]8346[/TD]
9 [TD="align: right"]1118[/TD]
[TD="align: right"]1546[/TD]
[TD="align: right"]1794[/TD]
[TD="align: right"]3277[/TD]
[TD="align: right"]4464[/TD]
[TD="align: right"]6314[/TD]
[TD="align: right"]7595[/TD]
[TD="align: right"]9917[/TD]
10 [TD="align: right"]1119[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]3124[/TD]
[TD="align: right"]3374[/TD]
[TD="align: right"]6554[/TD]
[TD="align: right"]8722[/TD]
[TD="align: right"]9603[/TD]
[/TABLE]
Sub Sort_leftRight_numeric()
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
Display More