|
发表于 2016-7-12 22:05
|
显示全部楼层
本楼为最佳答案
1、分离成五列;2、按各列排序;3、恢复成一列。- Sub tt()
- arr = [a1].CurrentRegion
- r = UBound(arr)
- ReDim brr(1 To r, 10)
- For i = 1 To r
- xrr = Split(arr(i, 1), "/")
- For j = 0 To UBound(xrr)
- brr(i, j) = xrr(j)
- Next
- Next
- [e1].Resize(r, 5) = brr '辅助数组
- cx = Array(3, 1, 2, 4, 5) '排序各列的先后次序
- With ActiveSheet.Sort '对辅助数组排序
- .SortFields.Clear
- For x = 0 To UBound(cx)
- c = cx(x) '参与排序的列
- .SortFields.Add Key:=Cells(1, c + 4), Order:=xlAscending
- Next
- .SetRange [e1].Resize(r, 5)
- .Header = xlGuess
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- arr = [e1].Resize(r, 5) '辅助数组恢复成原始格式
- ReDim brr(1 To r, 1 To 1)
- For i = 1 To r
- brr(i, 1) = Join(Application.Index(arr, i), "/")
- Next
- [e1].Resize(r, 5) = ""
- [e1].Resize(r) = brr
- End Sub
复制代码 |
|