多条件自定义排序

wangzqxa

遇到多条件自定义排序——一个比较棘手的问题，请大神指教如何用VBA才能做到：
A列（第一排序条件）：按一队、二队、三队
B列（第二排序条件）：按甲1、甲2、甲3、甲4、甲5、甲6、甲7、甲8、甲9
F列（第三排序条件）：按升序
C列（第四排序条件）：按升序

谢谢赐教！！

wangzqxa

请看附件，谢谢赐教！

zhaogang1960

请参考：

1. Sub Macro1()
   
2.     Application.ScreenUpdating = False
   
3.     With Range("A3:R" & Range("A65536").End(xlUp).Row)
   
4.         .Sort Key1:=Range("F3"), Order1:=1, key2:=Range("C3"), order2:=1, Header:=xlNo
   
5.         Application.AddCustomList ListArray:=Array("甲1", "甲2", "甲3", "甲4", "甲5", "甲6", "甲7", "甲8", "甲9")
   
6.         .Sort Key1:=Range("b3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
   
7.         Application.DeleteCustomList ListNum:=Application.CustomListCount
   
8.         Application.AddCustomList ListArray:=Array("一队", "二队", "三队")
   
9.         .Sort Key1:=Range("a3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
   
10.         Application.DeleteCustomList ListNum:=Application.CustomListCount
    
11.     End With
    
12.     Application.ScreenUpdating = True
    
13. End Sub
    

复制代码

zhaogang1960

请测试附件
多条件自定义排序.rar (38.82 KB, 下载次数: 1176)

2014-2-14 18:34 上传

点击文件名下载附件

wangzqxa

1. Sub Macro1()
   
2.     Application.ScreenUpdating = False
   
3.     With Range("A3:R" & Range("A65536").End(xlUp).Row)
   
4.         [color=Red].Sort Key1:=Range("F3"), Order1:=1, key2:=Range("C3"), order2:=1, Header:=xlNo
   
5. [/color]       [color=DarkGreen] Application.AddCustomList ListArray:=Array("甲1", "甲2", "甲3", "甲4", "甲5", "甲6", "甲7", "甲8", "甲9")
   
6.         .Sort Key1:=Range("b3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
   
7.         Application.DeleteCustomList ListNum:=Application.CustomListCount[/color]
   
8.         [color=Blue]Application.AddCustomList ListArray:=Array("一队", "二队", "三队")
   
9.         .Sort Key1:=Range("a3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
   
10.         Application.DeleteCustomList ListNum:=Application.CustomListCount
    
11. [/color]    End With
    
12.     Application.ScreenUpdating = True
    
13. End Sub

复制代码

谢谢斑竹，太牛了。想问一下，如果再增加自定义排序条件的话，可不可以按标色的代码添加？红色字体代表F列、C列的升序排列？请斑竹赐教！

wangzqxa

1. Sub Macro1()
   
2.     Application.ScreenUpdating = False
   
3.     With Range("A3:R" & Range("A65536").End(xlUp).Row)
   
4.        [color=Red] .Sort Key1:=Range("F3"), Order1:=1, key2:=Range("C3"), order2:=1, Header:=xlNo[/color]
   
5.         [color=DarkGreen]Application.AddCustomList ListArray:=Array("甲1", "甲2", "甲3", "甲4", "甲5", "甲6", "甲7", "甲8", "甲9")
   
6.         .Sort Key1:=Range("b3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
   
7.         Application.DeleteCustomList ListNum:=Application.CustomListCount
   
8. [/color]        [color=Purple]Application.AddCustomList ListArray:=Array("一队", "二队", "三队")
   
9.         .Sort Key1:=Range("a3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
   
10.         Application.DeleteCustomList ListNum:=Application.CustomListCount
    
11. [/color]    End With
    
12.     Application.ScreenUpdating = True
    
13. End Sub
    
14. End Sub

复制代码

谢谢斑竹，太牛了。想问一下，如果再增加自定义排序条件的话，可不可以按标色的代码添加？红色字体代表F列、C列的升序排列？请斑竹赐教！[/code]

wangzqxa

Sub Macro1()
    Application.ScreenUpdating = False
    With Range("A3:R" & Range("A65536").End(xlUp).Row)
       .Sort Key1:=Range("F3"), Order1:=1, key2:=Range("C3"), order2:=1, Header:=xlNo
        Application.AddCustomList ListArray:=Array("甲1", "甲2", "甲3", "甲4", "甲5", "甲6", "甲7", "甲8", "甲9")
        .Sort Key1:=Range("b3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
        Application.DeleteCustomList ListNum:=Application.CustomListCount
        Application.AddCustomList ListArray:=Array("一队", "二队", "三队")
        .Sort Key1:=Range("a3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
        Application.DeleteCustomList ListNum:=Application.CustomListCount
    End With
    Application.ScreenUpdating = True
End Sub
End Sub[/code]谢谢斑竹，太牛了。想问一下，如果再增加自定义排序条件的话，可不可以按标色的代码添加？红色字体代表F列、C列的升序排列？请斑竹赐教！

wangzqxa

Sub Macro1()
    Application.ScreenUpdating = False
    With Range("A3:R" & Range("A65536").End(xlUp).Row)
        .Sort Key1:=Range("F3"), Order1:=1, key2:=Range("C3"), order2:=1, Header:=xlNo
        Application.AddCustomList ListArray:=Array("甲1", "甲2", "甲3", "甲4", "甲5", "甲6", "甲7", "甲8", "甲9")
        .Sort Key1:=Range("b3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
        Application.DeleteCustomList ListNum:=Application.CustomListCount
       Application.AddCustomList ListArray:=Array("一队", "二队", "三队")
        .Sort Key1:=Range("a3"), Order1:=1, Header:=xlNo, OrderCustom:=Application.CustomListCount + 1
        Application.DeleteCustomList ListNum:=Application.CustomListCount
    End With
    Application.ScreenUpdating = True
End Sub
End Sub[/code]谢谢斑竹，太牛了。想问一下，如果再增加自定义排序条件的话，可不可以按标色的代码添加？红色字体代表F列、C列的升序排列？请斑竹赐教！

zhaogang1960

共有3个排序：
第一个是F列、C列的升序排列
第二个是对B列自定义排序，修改数组可添加条件
第三个是对A列自定义排序

wangzqxa

谢谢赐教！又碰到一个问题，排序一次很好，如果排序第二次就会出错！