[µo°Ý] ¬O§_¥i¥H²¤Æ¿ý»s«áªºVBA »yªk

jsc0518

Dear all,
§Ú¨Ï¥Î¿ý»s¤è¦¡»s§@VBA
(1).¿ï¾ÜDÄæ¡Acopy
(2).¿ï¾ÜUÄæ¡A¿ï¾Ü©Ê¶K¤W-­È
(3).¸ê®Æ(ÂI¿ï¤u¨ã¦C)-²¾°£­«½Æ¶µ
    ** ActiveSheet.Range("$U$1:$U$20000"). --> ³o¤@¬q¬O§Ú¥Î¤â°Ê§ïªº¡A¦]¬°¥H«áÁÙ·|¦³¦hªº¸ê®Æ²£¥X
(4).¿ï¾ÜUÄæ¡Acopy
(5).¿ï¾ÜPÄæ¡A¿ï¾Ü©Ê¶K¤W-­È
(6).¿ï¾ÜUÄæ¡A²M°£¸ÓÄæ¦ì¸ê°T



¤U­±¬O¿ý»sªº»yªk¡A¬O§_¥i¥H¦A²¤Æ©O¡H

Sub Repeat()

    Columns("D:D").Select
    Selection.Copy
    Columns("U:U").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Range("$U$1:$U$20000").RemoveDuplicates Columns:=1, Header:=xlYes
    Selection.Copy
    Columns("P:P").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Columns("U:U").Select
    Selection.ClearContents


    Range("Q1").Select

End Sub

jsc0518

¦^´_ 12# singo1232001

Dear singo1232001,
·PÁ±zªº¦^ÂФθɥR¡A´ú¸ÕOK¡AÁ°աI

singo1232001

¦^´_ 11# jsc0518

µ¹jsc0518¤j°Ñ¦Ò
À°M¤j¸É¥R¤@¤U
Sub Macro2()
     Columns( "P:P").ClearContents  '<--¥[³o¦æ¥i­«½Æ¨Ï¥Î ¦]AdvancedFilter ªº¶K¤W¦ì¸m¶·¬°ªÅ
    Columns("D:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( "P:P"), Unique:=True
    [P1].Delete Shift:=xlUp '¦pªG¤£­nªíÀY ¥[¤W³o¦æ
End Sub

jsc0518

¦^´_ 10# ML089
Hi ±ß¤W¦n¡I
·PÁ±zªº¼ö¤ß«ü¾É¤Î¦^ÂÐ
¬O¹L¤FVBA¡ATEST OK¡A³t«×¶W§Ö
·PÁ±zÅo¡I

ML089

³o¬O D:D ¸ê®Æ½Æ»s¤£­«½Æ¸ê®Æ¦Ü P:P¡A¦³§tªíÀY


Sub Macro2()
    Columns("D:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( "P:P"), Unique:=True
End Sub

jsc0518

¦^´_ 6# samwang
Hi ÁÂÁ±zªº¸ê°T¡A§Ú§ä¬Ý¬Ý¸ê®Æ

jsc0518

¦^´_ 7# singo1232001
Hi ·PÁ±zªº¦^ÂмڡA§Ú¸Õ¸Õ¬Ý¡I

singo1232001

¦^´_ 5# jsc0518

¤§«e ³o­Ó¼gªk§Ú¤@ª½¨S·dÀ´
­ì¥ý¤]¥H¬°¬O§R°£­«½Æªº¥Îªk
¸g¹L¤j¤j´£¥Ü«üÂI §Ú¤]¶]¥h¬d¤F¸ê®Æ ²×©ó¤F¸Ñ³o­Ó·N«ä
https://www.twblogs.net/a/5ca5b36bbd9eee59d332e201
¤~§¹¥þ·d©ú¥Õ ¥L¥»¨­¬O¤@ºØ²K¥[¸ê®Æªº¥\¯à  
¦ý¦]¬°¦r¨å²K¥[¦³°ß¤@©Ê(­«½Æªº¸ê®Æ·|¥[¤£¶i¥h) ©Ò¥H´Nµ²ªG¨Ó»¡Åܦ¨¨ã¦³¿z¿ï­«½Æªº®ÄªG

¥t¥~©^¤W»P¤j¤j¤@¼Ëªº¼gªk
Sub test1()
Dim xD, i&
Set xD = CreateObject("Scripting.Dictionary")
For Each d In Range("D1:D" & [d65536].End(3).Row).Value
xD(d) = "": Next   'xD(d)="" ¬O¤@ºØÁY¼g ­ì¥»¬O­n¼g¦¨ xD.Item(d) = ""
Range("P1").Resize(xD.Count) = Application.Transpose(xD.keys)
End Sub
·PÁ¤j¤j«üÂI

samwang

¦^´_ 5# jsc0518


¨S¦³§R°£­«½Æ©Êªº»yªk¡A¨º¬O¦r¨å¯S©Ê¥\¯à¤§¤@¡A±qºô¯¸¤º¥i§ä¨ì«Ü¦h¬ÛÃöªº¸ê°T¡AÁÂÁÂ

jsc0518

¦^´_ 3# samwang
Dear samwang,

±z¦n¡I·PÁ±zªº¼ö¤ß¦^ÂСA»yªk¥i¥H¨Ï¥Î¡C
½Ð±Ð¤@¤U
1.­þ¬q¬O"§R°£­«½Æ©Ê¸ê®Æ"ªº»yªk©O¡H
2."¥Î¦r¨åªº¼gªk"¬O¬Æ»ò·N«ä©O¡H

Thank you.