[µ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

singo1232001

¦^´_ 1# jsc0518

Sub RepeatA()
Columns("U") = Columns("D").Value
Range("U1:U20000").RemoveDuplicates Columns:=1, Header:=xlYes
Columns("P") = Columns("U").Value
Columns("U").ClearContents
End Sub

samwang

¦^´_ 1# jsc0518

¥Î¦r¨åªº¼gªk¡A½Ð¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test()
Dim Arr, xD, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range("D1:D" & [d65536].End(3).Row)
For i = 1 To UBound(Arr): xD(Arr(i, 1) & "") = "": Next
Range("P1").Resize(xD.Count) = Application.Transpose(xD.keys)
End Sub

jsc0518

¦^´_ 2# singo1232001
Dear singo1232001,
±z¦n¡I·PÁ±zªº¼ö¤ß¦^´_¡C
»yªk¥i¥H¨Ï¥Î¡A¦ý°õ¦æ¹Lµ{¤¤³t«×«ÜºC¡A¥t¥~¥u­n§R°£¤@Äæ¦ì¤º¸ê®Æ¡AVBA¤]·|¶]
ÁÂÁ¼Ú

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.

samwang

¦^´_ 5# jsc0518


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

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

jsc0518

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

jsc0518

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

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