Hsieh

¦^´_ 1# terrykyo520

1. Sub ex()
   
2. Dim A As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For Each A In Range([B1], [B1].End(xlDown))
   
5. If d.exists(A & A.Offset(, 1)) = False Then d(A & A.Offset(, 1)) = A.Offset(, -1).Resize(, 3).Value
   
6. Next
   
7. [A:C].ClearContents
   
8. [A1].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
   
9. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 5# terrykyo520
¸Õ¸Õ¡I

1. Public Sub ex()
   
2. Dim ar()
   
3. arr = Range("a2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
   
4. k = UBound(arr)
   
5. For i = 1 To UBound(arr) - 1
   
6.     For j = i + 1 To UBound(arr)
   
7.         If arr(i, 1) = "" Or arr(j, 1) = "" Then GoTo 10
   
8.         If arr(i, 2) & arr(i, 3) = arr(j, 2) & arr(j, 3) Then
   
9.             arr(j, 1) = ""
   
10.             arr(j, 2) = ""
    
11.             arr(j, 3) = ""
    
12.             k = k - 1
    
13.         End If
    
14. 10:
    
15.     Next
    
16. Next
    
17. 
    
18. ReDim ar(1 To k, 1 To 3)
    
19. k = 1
    
20. For i = 1 To UBound(arr)
    
21.     If arr(i, 1) <> "" Then
    
22.         ar(k, 1) = arr(i, 1)
    
23.         ar(k, 2) = arr(i, 2)
    
24.         ar(k, 3) = arr(i, 3)
    
25.         k = k + 1
    
26.     End If
    
27. Next
    
28. Range("a2:C" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    
29. [a2].Resize(UBound(ar), 3) = ar
    
30. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 5# terrykyo520


    ½Ð¦C¤@´Á±æªºµ²ªGªí¬O¤°»ò
³o¼Ë¤ñ¸û¦n³B²z

terrykyo520

¦^´_ 4# Scott090
§Ú®M¥Î¤U¥hµ²ªG©Ç©Çªº~·|ªÅ¤@¦æ¤S ¦A¤U¤@¦æA¦Cªº­È¶]¨ìB¥h B¦Cªº­È¶]¨ìC¦C C¦Cªº­È¤£¨£
¥i§_½ÐÀ°§Ú¨Ì³oTESTªºEXCEL¤U¥h¬Ý¬Ý©O~~ÁÂÁ±оÇ~
·Q­n°õ¦æ«áªºµ²ªG¦p¹Ï~¥ª°õ¦æ«e~¥k¬°°õ¦æ«á

¤U¸ü (54.94 KB)

2015-8-11 11:45

TEST.rar (1.59 KB)

¤U¸ü¦¸¼Æ: 10

2015-8-11 11:47

Scott090

¦^´_ 3# terrykyo520


   ³o­ÓÀ³¨S¦³ª©¥»ªº°Ï§O

1. Sub Test1()
   
2.     Dim i&, j&
   
3.     Dim aa, bb
   
4.    
   
5.     i = [A1].End(xlDown).Row
   
6.     aa = Range("A2:C" & i).Value
   
7.     For i = 1 To UBound(aa) - 1
   
8.         For j = i + 1 To UBound(aa)
   
9.             If aa(j, 2) = aa(i, 2) And aa(j, 3) = aa(i, 3) Then
   
10.                 aa(j, 1) = "": aa(j, 2) = "": aa(j, 3) = ""
    
11.             End If
    
12.         Next
    
13.     Next
    
14.     ReDim bb(UBound(aa), 3)
    
15.     j = 0
    
16.     For i = 1 To UBound(aa)
    
17.         If aa(i, 1) <> "" Then
    
18.             j = j + 1
    
19.             bb(j, 1) = aa(i, 1): bb(j, 2) = aa(i, 2): bb(j, 3) = aa(i, 3)
    
20.         End If
    
21.     Next
    
22.     Range("A2").Resize(UBound(aa), 3).Clear
    
23.     Range("A2").Resize(UBound(bb), 3) = bb
    
24. 
    
25. End Sub

½Æ»s¥N½X

terrykyo520

¦^´_ 2# Scott090


RemoveDuplicates³o¥u¦³2007¥H¤Wª©¥»¤~¥i¥Î
§Úªºª©¥»¬O2003ªº
ÁÂÁÂ

Scott090

¦^´_ 1# terrykyo520


    ¸Õ¸Õ¬Ý³o­Ó¬O§_²Å¦X»Ý­n

1. Sub Test0()
   
2.     Dim H&
   
3.     Range("A2").Select
   
4.     H = [A1].End(xlDown).Row
   
5.     Range("$A$1:$C$" & H).RemoveDuplicates Columns:=Array(2, 3), Header:=xlYes
   
6. End Sub

½Æ»s¥N½X