terrykyo520

¦^´_ 8# lpk187
ÁÂÁÂ~¥i¥H­C~

½Ð°Ý³o¦æªº·N«ä¬O
Cells(Rows.Count, 1).End(xlUp).Row

¦pªG§Úªº²£«~¦b²ÄM¦C ¦a°Ï¦b²ÄQ¦C  ¸ê®ÆÁ`¦@¨ìAD¦C
¬O­n§ï¦¨
arr = Range("a2:AD" & Cells(Rows.Count, 1).End(xlUp).Row)

­n«ç»ò­×§ï©O~ÁÂÁÂ

terrykyo520

¦^´_ 9# Hsieh

OK¥i¦æ­C
§Ú¥u¬Ý±oÀ´ Dim A As Range
³Ñ¤U~¦nÃø

¦pªG§Úªº²£«~¦b²ÄM¦C ¦a°Ï¦b²ÄQ¦C  ¸ê®ÆÁ`¦@¨ìAD¦C
­n«ç»ò­×§ï©O~ÁÂÁÂ

terrykyo520

¦^´_ 10# Scott090


    ÁÂÁÂÀ°¦£~¥Ø«e§ÚµLªk¤U¸üÀÉ®×
    ¥u¯à¥ý»¡Án3Q~¤§«á¦A¤U¸ü¬Ý¬Ý¼gªk~~

lpk187

¦^´_ 12# terrykyo520

Cells(Rows.Count, 1).End(xlUp).Row¬OAÄ檺³Ì«á¤@®æªº¦C¸¹¡ARows.Count¬°©Ò¦³ªº¦C¸¹¡A1´N¬OAÄæ
­ì¨ÓªºÄæ¼Æ¥u¦³¨Ç3Äæ¡A©Ò¥H¼gªº¤ñ¸û²³æ¡A¦ý¬O­Y¸ê®Æ¼Æ¶q«Ü¦h®É´N¤£¯à¦p¤U­±ªí¥Üªº¼gªk¡A
            arr(j, 1) = ""
            arr(j, 2) = ""
            arr(j, 3) = ""
            ...
            ....
            .......
            .......
            arr(j, X) = ""
³o¼Ë¼g·|²Ö¦ºªº
©Ò¥H¥²¶·¦h¤@­Ó°j°é

1. Public Sub ex()
   
2. Dim ar()
   
3. arr = Range("A2:AD" & 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, 13) & arr(I, 17) = arr(j, 13) & arr(j, 17) Then
   
9.             For L = 1 To UBound(arr, 2)
   
10.                 arr(j, L) = ""
    
11.             Next
    
12.             K = K - 1
    
13.         End If
    
14. 10:
    
15.     Next
    
16. Next
    
17. 
    
18. ReDim ar(1 To K, 1 To UBound(arr, 2))
    
19. K = 1
    
20. For I = 1 To UBound(arr)
    
21.     If arr(I, 1) <> "" Then
    
22.         For L = 1 To UBound(arr, 2)
    
23.             ar(K, L) = arr(I, L)
    
24.         Next
    
25.         K = K + 1
    
26.     End If
    
27. Next
    
28. Range("a2:AD" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    
29. [a2].Resize(UBound(ar), UBound(arr, 2)) = ar
    
30. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 14# terrykyo520

½Ðª`·N°}¦C°_©l¦ì¸m ¬O 0 ÁÙ¬O 1
¥[ Option base 1 ¬O©w¸q°}¦C±q 1 ¶}©l

°Ñ·Ó 4#

Option base 1   
Sub Test1()
    Dim i&, j&
    Dim aa, bb
   
    i = [A1].End(xlDown).Row
    aa = Range("A2:C" & i).Value
   For i = 1 To UBound(aa) - 1
.........

terrykyo520

¦^´_ 11# GBKEE


³o¨âºØ¤]¥i¥H­C~¦n¦h¤èªk
¦pªG§Úªº²£«~¦b²ÄM¦C ¦a°Ï¦b²ÄQ¦C  ¸ê®ÆÁ`¦@¨ìAD¦C
¬O­n§ï¦¨
Sub Ex()
    Dim R As Range, Rng As Range
    With ActiveSheet.UsedRange
        .Columns("M:Q").AdvancedFilter Action:=xlFilterInPlace, Unique:=True


Sub Ex1()
    Dim Sh As Worksheet, wb As Workbook
    Set Sh = ActiveSheet
    Sh.Copy
    Set wb = ActiveWorkbook
    With wb.Sheets(1)
        .UsedRange.Columns("M:Q").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

§ï³o¨âÃä¶Ü~TKS

greetingsfromtw

¤£¦n·N«ä­É¼ÐÃD°Ý¤@¤U,

¦³¸ÕµÛ±Nlpk187¤jªºµ{¦¡½X²¤°µ­×§ï,

¦]¬°¹ïVBA¯uªºÁÙ¬O·s¤âµ{«×,©Ò¥H­×§ïªº³¡¥÷°µ±o«Ü¤£¦n,

¥ý²­z¥Ø«e§Æ±æ¹F¨ìªº®ÄªG:
1.­Y¨â¾î¦CªºJÄæ¼Æ­È¤£¦P,LÄæ¼Æ­È¬Û¦P,
«h¶È²M°£¨ä¤¤¤@¾î¦CªºLÄæ¼Æ­È,¨â¾î¦C§¡¤£§R°£.

2.¸ò¤W­z³W«hÃþ¦ü,
­Y¨â¾î¦CªºJÄæ¼Æ­È¬Û¦P,LÄæ¼Æ­È¤£¦P,
«h¶È²M°£¨ä¤¤¤@¾î¦CªºJÄæ¼Æ­È,¨â¾î¦C§¡¤£§R°£.

3.­Y¨â¾î¦CªºJÄæ¼Æ­È¤ÎLÄæ¼Æ­È§¡¬°ªÅ¥Õ,
«h¨â¾î¦C§¡§R°£.
­×§ï«áµ{¦¡½X¦p¤U:

1. Public Sub extwo()
   
2. Dim ar()
   
3. Range("c2").Resize(Cells(Rows.Count, 3).End(xlUp).Row, 1).Select
   
4. Selection.Resize(Selection.Rows.Count - 1, 1).Select
   
5. Selection.Copy Range("a2")
   
6. 
   
7. arr = Range("A2:AD" & Cells(Rows.Count, 1).End(xlUp).Row)
   
8. K = UBound(arr)
   
9. For i = 1 To UBound(arr) - 1
   
10.     For j = i + 1 To UBound(arr)
    
11.         If arr(i, 1) = "" Or arr(j, 1) = "" Then GoTo 10
    
12.         If arr(i, 11) & arr(i, 13) = arr(j, 11) & arr(j, 13) Then
    
13.             For L = 1 To UBound(arr, 2)
    
14.                 arr(j, L) = ""
    
15.             Next
    
16.             K = K - 1
    
17.         ElseIf arr(i, 11) = arr(j, 11) And arr(i, 13) <> arr(j, 13) Then
    
18.             If arr(i, 13) = "" Then
    
19.             arr(i, 11) = ""
    
20.             Else
    
21.             arr(j, 11) = ""
    
22.             End If
    
23. 
    
24.         ElseIf arr(i, 11) <> arr(j, 11) And arr(i, 13) = arr(j, 13) Then
    
25.             If arr(i, 11) = "" Then
    
26.             arr(i, 13) = ""
    
27.             Else
    
28.             arr(j, 13) = ""
    
29.             End If
    
30. 
    
31.         ElseIf arr(i, 11) = "" And arr(i, 13) = "" Then
    
32.         arr(i, 1) = ""
    
33. 
    
34.         ElseIf arr(j, 11) = "" And arr(j, 13) = "" Then
    
35.         arr(j, 1) = ""
    
36. '
    
37.         End If
    
38.         
    
39. 10:
    
40.     Next
    
41. Next
    
42. 
    
43. ReDim ar(1 To K, 1 To UBound(arr, 2))
    
44. K = 1
    
45. For i = 1 To UBound(arr)
    
46.     If arr(i, 1) <> "" Then
    
47.         For L = 1 To UBound(arr, 2)
    
48.             ar(K, L) = arr(i, L)
    
49.         Next
    
50.         K = K + 1
    
51.     End If
    
52. Next
    
53. Range("a2:AD" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    
54. [a2].Resize(UBound(ar), UBound(arr, 2)) = ar
    
55. 
    
56. Columns(1).ClearContents
    
57. [L2].Select
    
58. End Sub

½Æ»s¥N½X

¥Ø«eªº°ÝÃD¦b©ó,

´ú¸Õ¤Ö¶q¸ê®Æ®É(¤ñ¦p»¡10µ§¥H¤º),

¬Ý¦ü¨S¦³°ÝÃD,

¦ý¬O­Y´ú¸Õ¤j¶q¸ê®Æ®É,

¥ÎEXCEL2007ªº¤º«Øªº®æ¦¡¤Æ±ø¥ó³]©w,

¥h¼Ð¥X­«½Æªº­È®É,

·|µo²{ÁÙ¬O¦³«Ü¦h­«½Æ¸ê®Æ¨S³Q§R°£,

»Ý¦A¶]²Ä¤G¦¸µ{¦¡½X,

¤~·|²M°£,

¤£ª¾°ÝÃD¥X¦b­þ¸Ì,

ÁÙ±æ«e½ú¤£§[«üÂI,¤Q¤À·PÁÂ.