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]
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]
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