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

n7822123

¦^´_ 17# lilizzzz


Range("A1:AK300000")   

½d³ò¤Ó¤j¤F¡A30¸U¦C?¡A«Øij¥ý§PÂ_½d³ò¦A°µ¿z¿ï¡A¥i´î¤Ö¦h¾lªº­pºâ¡C

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

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

samwang

¦^´_ 13# lilizzzz


    ¬°¬Æ»ò·|¥Î i&  j% n& ' ¨Ó«Å§i ?
-->¥i°Ñ¦Ò¦p¤Uºô§}¸ÑÄÀ¡AÁÙÆZ¸Ô²Óªº¡AÁÂÁ¡C
http://forum.twbts.com/viewthrea ... amp;page=1#pid22366
Dim i%´N¬ODim i As IntegerªºÂ²¼g
¤@¨Ç±`¥Îªº¥Nªí²Å¸¹¦p¤U
Integer ªº«¬ºA«Å§i¦r¤¸¬O¦Ê¤À¤ñ²Å¸¹ %
Long ªº«¬ºA«Å§i¦r¤¸¬° &
Double ªº«¬ºA«Å§i¦r¤¸¬O¼Æ¦r²Å¸¹ #
Single ªº«¬ºA«Å§i¦r¤¸¬° !
String ªº«¬ºA«Å§i¦r¤¸¬° $

lilizzzz

¦^´_ 11# quickfixer


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

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

lilizzzz

¦^´_ 9# samwang


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

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

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µ{¦¡

quickfixer

¦^´_ 7# lilizzzz

²q´ú¥i¯à¬O¸ê®Æ¤Ó¦h,¥Î¦r¦ê³B²zªø«×¤Óªø
§ï¥Îrange¶°¦X¨Ó§R,½Ð´ú¸Õ

    Sub test()

    Dim dr As Range
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

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

    Set dic = CreateObject("scripting.dictionary")
    For i = Range("A2000").End(3).Row To 1 Step -1
        If dic.exists(Cells(i, "A").Value) Then
            If dr Is Nothing Then
                Set dr = Rows(i)
            Else
                Set dr = Union(dr, Rows(i))
            End If
        Else
            dic(Cells(i, "A").Value) = ""
        End If
    Next i

    dr.EntireRow.Delete
   
    Set dr = Nothing
    Set dic = Nothing
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True

End Sub

samwang

¦^´_ 6# lilizzzz


    ½Ð°Ý¥i¥H´£¨Ñ¸ê®Æ¶Ü? §Ú¦A½T»{°ÝÃD¡A ÁÂÁ¡C