按字母先后排序

张雄友

按字母先后排序。

橘子红

写的不好,方法太笨,仅供参考

1. Sub x()
   
2. Dim r, x%, ro%, s%, rng
   
3. ro = [a65500].End(3).Row
   
4. Set r = CreateObject("vbscript.regexp")
   
5.   r.Global = True
   
6.   r.Pattern = "[^0-9]+|[0-9]+"
   
7.    For Each rng In Range("a1:a" & ro)
   
8.      s = 1
   
9.        For Each mat In r.Execute(rng)
   
10.          rng(1, s + 4) = IIf(s = 2, Val(mat), mat)
    
11.          s = s + 1
    
12.         Next
    
13.    Next
    
14.    Range("e1").CurrentRegion.Sort key1:=[e1], order1:=xlAscending, key2:=[f1], order2:=xlAscending, Header:=xlNo
    
15.    For x = 1 To ro
    
16.        Cells(x, 1) = Cells(x, 5) & Cells(x, 6)
    
17.    Next
    
18. End Sub
    

复制代码

dsmch

1. Sub Macro1()
   
2. Dim arr, brr, i&
   
3. arr = Range("a1").CurrentRegion
   
4. ReDim brr(1 To UBound(arr), 1 To 2)
   
5. With CreateObject("vbscript.regexp")
   
6.     .Pattern = "\d"
   
7.     .Global = True
   
8.     For i = 1 To UBound(arr)
   
9.         brr(i, 1) = .Replace(arr(i, 1), "")
   
10.         brr(i, 2) = Replace(arr(i, 1), brr(i, 1), "")
    
11.     Next
    
12. End With
    
13. Range("b1").Resize(UBound(brr), 2) = brr
    
14. Range("a1").CurrentRegion.Sort Key1:=[b1], Key2:=[c1], Header:=xlGuess
    
15. [b:c] = ""
    
16. End Sub

复制代码

dsmch

数组排序比较麻烦，一般不采用。