|
发表于 2012-2-6 23:27
|
显示全部楼层
本楼为最佳答案
yjwdjfqb 发表于 2012-2-6 23:13
c = Range("iv1").End(xlToLeft).Column '数据源最右侧列号
如果表头不在第一行,也有问题
- Sub 科室排序()
- Dim arr, i&, c, r
- '设置自定义排序的顺序
- arr = Array("外妇科", "手术室", "内儿科", "西医科", "中医科", "耳鼻喉科", "放射科", "检验科", "B超室", "口腔科", "针灸科", "西药房", "中药房", "收费室", "疾控科", "合管办", "后勤科")
- If Not Cells.Find("序号") Is Nothing Then
- Set Rng = Cells.Find("序号").CurrentRegion
- If Not Intersect(Rng, ActiveCell) Is Nothing Then
- With Application
- .ScreenUpdating = False
- .AddCustomList ListArray:=arr
- i = .GetCustomListNum(arr) '返回字符串数组的自定义序列号
- c = Rng.Item(1, 1).End(xlToRight).Column '数据源最右侧列号
- r = Cells(65536, ActiveCell.Column).End(xlUp).Row '所选单元格所在列最后一个数据的行号
- '排序区域
- With Range(Cells(ActiveCell.Row, Rng.Column), Cells(r, c))
- '设置排序的起始单元格
- .Sort key1:=ActiveCell, order1:=xlAscending, _
- Header:=xlNo, OrderCustom:=i + 1
- End With
- .DeleteCustomList ListNum:=i
- .ScreenUpdating = True
- MsgBox "排序完成", vbInformation
- End With
- End If
- End If
- End Sub
复制代码
附件6.rar
(20.63 KB, 下载次数: 11)
|
|