PKKO

¦^´_ 1# maiko

¸Õ¸Õ¬Ý

1. Sub test()
   
2. '
   
3.    
   
4.     Columns("A:A").Copy
   
5.     Columns("G:G").PasteSpecial Paste:=xlPasteValues '¶K¤W­È
   
6.     Application.CutCopyMode = False
   
7.     '²¾°£­«½Æ
   
8.     ActiveSheet.Range("G1:G" & [g65536].End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
   
9.     '±Æ§Ç
   
10.     ActiveWorkbook.Worksheets("¤u§@ªí1").Sort.SortFields.Add Key:=Range("G1:G" & [g65536].End(xlUp).Row), _
    
11.         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
12.     With ActiveWorkbook.Worksheets("¤u§@ªí1").Sort
    
13.         .SetRange Range("G1:G" & [g65536].End(xlUp).Row)
    
14.         .Header = xlYes
    
15.         .MatchCase = False
    
16.         .Orientation = xlTopToBottom
    
17.         .SortMethod = xlPinYin
    
18.         .Apply
    
19.     End With
    
20. End Sub

½Æ»s¥N½X

PKKO

¦^´_ 3# maiko
§Ú­ì¥»´£¨Ñªºµ{¦¡½X¬°,½Æ»sAÄæ¨ìGÄæ=>²¾°£­«½Æ(«O¯dªíÀY)=>±Æ§Ç(­^¤å¤è¦¡)
¤£À´§A©Ò¿×ªº¸ê®ÆÅçÃÒ,­Y§A«üªº¬O¤U©Ô¿ï³æ
¸Õ¸Õ¬Ý,ÀÉ®×¥i¥H¥¿±`¹B§@
test.rar (16.45 KB)

¤U¸ü¦¸¼Æ: 5

2015-4-21 19:53

1. Sub test()
   
2. '
   
3.    
   
4.     Columns("A:A").Copy
   
5.     Columns("G:G").PasteSpecial Paste:=xlPasteValues '¶K¤W­È
   
6.     Application.CutCopyMode = False
   
7.     '²¾°£­«½Æ
   
8.     ActiveSheet.Range("G1:G" & [g65536].End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
   
9.     '±Æ§Ç
   
10.     ActiveWorkbook.Worksheets("¤u§@ªí1").Sort.SortFields.Add Key:=Range("G1:G" & [g65536].End(xlUp).Row), _
    
11.         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
12.     With ActiveWorkbook.Worksheets("¤u§@ªí1").Sort
    
13.         .SetRange Range("G1:G" & [g65536].End(xlUp).Row)
    
14.         .Header = xlYes
    
15.         .MatchCase = False
    
16.         .Orientation = xlTopToBottom
    
17.         .SortMethod = xlPinYin
    
18.         .Apply
    
19.     End With
    
20.     For i = 2 To [g65536].End(xlUp).Row
    
21.         name1 = name1 & Cells(i, 7) & ","
    
22.     Next
    
23.     name2 = Mid(name1, 1, Len(name1) - 1)
    
24.     With Range("G2:G" & [a65536].End(xlUp).Row).Validation
    
25.         .Delete
    
26.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    
27.         xlBetween, Formula1:=name2
    
28.         .IgnoreBlank = True
    
29.         .InCellDropdown = True
    
30.         .InputTitle = ""
    
31.         .ErrorTitle = ""
    
32.         .InputMessage = ""
    
33.         .ErrorMessage = ""
    
34.         .IMEMode = xlIMEModeNoControl
    
35.         .ShowInput = True
    
36.         .ShowError = True
    
37.     End With
    
38.     Range("G2:G" & [g65536].End(xlUp).Row).ClearContents
    
39. End Sub

½Æ»s¥N½X