[µo°Ý] ¨D±Ï¦p¦óÁYµuVBA°õ¦æ®É¶¡

lilizzzz

¦U¦ì¥ý¶i¤j®a¦n:

¥H¤U¬O§Úªºªþ¥óvba¡A¬°½d¨ÒÀÉ¡A¹ê°È¸ê®Æ¦C¬ù¦³1500-2000µ§¥ª¥k¹p¦P¸ê°T

§Ú¹J¨ìªº°ÝÃD¬O¡A°õ¦æ³o¤@¬qvba®É¡A­nªá¶O¶W¦h®É¶¡¡A

§Ú¨ä¥L¤ävbaµ{¦¡ÂI¤@¤U´N¥i¥H¶]¥X¨Ó¡A·Q½Ð±Ð³o²Õµ{¦¡½X¬O­þ¸Ì§Ú¦³¼g¿ù¶Ü ? ¤~·|³y¦¨°õ¦æ®É¶¡¹L¤[¡A



' ¼È°±¥|­Ó®e©ö©ìºCªº Excel ¥\¯à
' ¼È°±¤½¦¡¦Û°Ê­pºâ
' ¼È°±µe­±§ó·s
Application.ScreenUpdating = False
' ¼È°±ª¬ºA¦C§ó·s
Application.DisplayStatusBar = False
' ¼È°±¨Æ¥ó³B²z
Application.EnableEvents = False

Sheets("¤u§@ªí1").Select
Range("A2").Select

'«Ø¥ß¦r¨å¡A§â¹ï¶H½á¤©dictionary¡A(µ¥©ó§PÂ_¨Ì¾Ú)
Set dic = CreateObject("scripting.dictionary")
'±qA¦C³Ì«á¤@¦C¶}©l¶i¦æ¿z¿ï: step -1
'¦pªG¬O±q²Ä¤@¦C¶}©l·|¦³§R°£¸õ¦æ±¡ªp¡AµLªk¹F¦¨§R°£­«½Æ®ÄªG
For i = Range("A65536").End(3).Row To 1 Step -1
'¦pªGAÄæ¼Æ¾Ú¤w¸g¦s¦b¦r¨å¤¤(µ¥©ó­«½Æ¤F)«h°õ¦æTHEN
If dic.exists(Cells(i, "A").Value) Then
'§R°£¸Ó¦C
Rows(i).Delete
Else
'­YAÄæ¼Æ¾Ú¥¼¦s©ó¦r¨å¤º¡A«h±N¨ä¥[¤J¦r¨å
dic(Cells(i, "A").Value) = ""
End If
Next i

' «ì´_¥|­Ó®e©ö©ìºCªº Excel ¥\¯à
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

·s¼W Microsoft Excel ¤u§@ªí.zip (19.53 KB)

¤U¸ü¦¸¼Æ: 13

2020-12-9 11:39

lilizzzz

¦^´_ 2# samwang

±z¦n¡A±zªºµ{¦¡½X¥i¥H¨Ï¥Î¡A«D±`·PÁ±z¡A¥t¥i½Ð±Ð±z¬õ¦â¨º´X¦Cªº·N«ä¶Ü ?

Sub tt()
Dim xD, Arr, i&, j%, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¨BÆJ¤@!V1], [¨BÆJ¤@!A65536].End(3))
    For i = UBound(Arr) To 1 Step -1
        If Not xD.Exists(Arr(i, 1)) Then
            N = N + 1
            xD(Arr(i, 1)) = N
            For j = UBound(Arr, 1) To 1
                Arr(N, j) = Arr(i, j)
            Next
        End If
    Next
Range("A1").Resize(UBound(Arr), UBound(Arr, 1)) = ""
Range("A1").Resize(N, UBound(Arr, 1)) = Arr
End Sub

lilizzzz

¦^´_ 3# quickfixer


±z¦n¡A¤µ¤é·|´ú¸Õ±z©Ò´£¨ÑVBAÅçÃÒ¡AÅçÃÒ«á·|¦A»P±z¦^´_¡A

«D±`·PÁ±zªº­@¤ß¸Ñµª¡AÁÂÁÂ

lilizzzz

¦^´_ 2# samwang


    ±z¦n¡A¦b´ú¸Õ±zªºµ{¦¡½X®Éµo²{·|¦³¸ê®Æ¨S§R°£¨ìªº±¡ªp

lilizzzz

¦^´_ 3# quickfixer


  ±z¦n¡A´ú¸Õ®É Range(dr).Delete Shift:=xlUp

·|¸õ¿ù

lilizzzz

¦^´_ 10# samwang


    ±z¦n¡AªþÀɧY¬O§Úªº¸ê®Æ¡A¥u¬O§Ú¹ê°È¤W¦A¹B¥Îªº¸ê®Æµ§¼Æ±q1500-30000³£¦³¥i¯à¡A

¤£¦n·N«ä³Â·Ð±z¤F¡AÁÂÁ¡A§Ú·|¥Î¤ß¥h§l¦¬³o¬qµ{¦¡

lilizzzz

¦^´_ 9# samwang


    ½Ð°Ý³o¤@¬q¡A
Dim xD, Arr, i&, j%, N& '

¬°¬Æ»ò·|¥Î i&  j% n& ' ¨Ó«Å§i ?

lilizzzz

¦^´_ 11# quickfixer


    «D±`ÁÂÁ±z¡A¤µ¤é¤U¯Z§Ú¦A¦^¥h´ú¸Õ¬Ý¬Ý±z³o¬qµ{¦¡½X¡A

¦ý¬O¦³ÂI²`¶ø§Ú»Ý­n¦hªá¤@ÂI®É¶¡¨Ó§l¦¬qq

lilizzzz

¦^´_ 11# quickfixer


    ±z¦n¡A³o¤@¥÷¥i¥H°õ¦æ®Ä²v¤ñ§Úªº§Ö¶W¦h¡A«D±`·PÁ±z¡A§Ú¨Ó¥h§l¦¬³o¤@¬q¡A

¥t·Q¸ò±z½Ð±Ð¡A§Ú³o¸Ì¦³¤@¬q¿ý»sªº¥¨¶°¡A¥\¯à¬O¿z¿ï¡A§Ú¦b¿ý»s®É­Ô·j´M³£«Ü§Ö¡A

¥i¬O¦Û¤v¦b°õ¦æ¥¨¶°®É¡A·j´M®Ä²v­n±Nªñ¤@¤ÀÄÁ¡A½Ð°Ý¬O§Ú¸ê®ÆÀÉ°ÝÃD¶Ü ?

    Sheets("3P-INSPPROD").Range("A1:AK300000").AdvancedFilter Action:=xlFilterCopy _
        , CriteriaRange:=Range("A1:C3"), CopyToRange:=Range("A7:AK7") _
        , Unique:=False

    ActiveWindow.SmallScroll Down:=-27
    Cells.Select

lilizzzz

¦^´_ 15# samwang


    ±z¦n¡A§Ú¦³¬Ý¨ì³o¤@­Ó»¡©ú¤F¡A­ì¨Ó¬O²¼g!«D±`ÁÂÁ±z

¥t·Q¸ò±z½Ð±Ð¡A§Ú³o¸Ì¦³¤@¬q¿ý»sªº¥¨¶°¡A¥\¯à¬O¿z¿ï¡A§Ú¦b¿ý»s®É­Ô·j´M³£«Ü§Ö¡A

¥i¬O¦Û¤v¦b°õ¦æ¥¨¶°®É¡A·j´M®Ä²v­n±Nªñ¤@¤ÀÄÁ¡A½Ð°Ý¬O§Ú¸ê®ÆÀÉ°ÝÃD¶Ü ?

    Sheets("3P-INSPPROD").Range("A1:AK300000").AdvancedFilter Action:=xlFilterCopy _
        , CriteriaRange:=Range("A1:C3"), CopyToRange:=Range("A7:AK7") _
        , Unique:=False

    ActiveWindow.SmallScroll Down:=-27
    Cells.Select