|
楼主 |
发表于 2017-5-29 11:11
|
显示全部楼层
Sub main()
Call 排序(6, 23)
Call 排序(24, 33)
Call 排序(34, 45)
End Sub
Sub 排序(r1, r2) '对以r1起始行,r2结束行的区域进行排序
Dim cel As Range
crr = Array(1, 26, 27) '要合并、撤销合并所在的列
For i = r1 To r2
Cells(i, "AB") = i '辅助列,用于合并单元格内的排序
For k = 0 To 2
j = crr(k)
Cells(i, j).UnMerge '取消合并单元格
If Cells(i, j) = "" Then Cells(i, j) = Cells(i - 1, j) '合并单元格取消后,给下面的空值赋值
Next
Next
Range("A" & r1 & ":AB" & r2).Sort key1:=Cells(r1, "z"), Order1:=xlDescending, key2:=Cells(r1, "ab") '排序
Application.DisplayAlerts = False
For i = r1 To r2 '合并单元格
For k = 0 To 2
j = crr(k)
Set cel = Cells(i, j)
If cel = cel.Offset(-1, 0) And Len(cel) > 0 Then cel.Offset(-1, 0).Resize(2, 1).Merge
Next
Next
[ab:ab] = "" '清空辅助列
Application.DisplayAlerts = True
End Sub |
|