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

samwang

¦^´_ 1# lilizzzz

½Ð½T»{¬O§_¬°±zªº»Ý¨D¡A·PÁÂ

Sub tt()
Dim xD, Arr, i&, j%, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¤u§@ªí1!U1], [¤u§@ªí1!A65536].End(3))
    For i = 1 To UBound(Arr)
        If Not xD.Exists(Arr(i, 1)) Then
            N = N + 1
            xD(Arr(i, 1)) = N
            For j = 1 To UBound(Arr, 2)
                Arr(N, j) = Arr(i, j)
            Next
        End If
    Next
Range("A1").Resize(UBound(Arr), UBound(Arr, 2)) = ""
Range("A1").Resize(N, UBound(Arr, 2)) = Arr
End Sub

quickfixer

¦^´_ 1# lilizzzz

±q²Ä¤@¦C©Î³Ì«á¤@¦C¶}©l³£¥i¥H
®É¶¡0.07=>0.003

    Sub test()

Dim dr As String
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
dr = dr & i & ":" & i & ","
Else
dic(Cells(i, "A").Value) = ""
End If
Next i

dr = Left(dr, Len(dr) - 1)
Range(dr).Delete Shift:=xlUp

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

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

·|¸õ¿ù

quickfixer

¦^´_ 7# lilizzzz


§A¥Î¦P¤@­ÓÀɮ׸ժº¶Ü?§Ú¥Î§AªºÀÉ®×´ú¸Õ¤£·|
§ï¦¨³o¼Ë¸Õ¸Õ
Sheets("¤u§@ªí1").Range(dr).EntireRow.Delete

samwang

¦^´_ 4# lilizzzz


Sub tt()
Dim xD, Arr, i&, j%, N& '
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¤u§@ªí1!U1], [¤u§@ªí1!A65536].End(3)) '­ì¸ê®Æ¸Ë¤J¼Æ²Õ
    For i = 1 To UBound(Arr)
        If Not xD.exists(Arr(i, 1)) Then
            N = N + 1   '­pºâ¤£­«½Æ°ß¤@­Èªº¦¸¼Æ
            xD(Arr(i, 1)) = N   '¤£­«½Æ°ß¤@­Èªº¸Ë¤J¦r¨å
            For j = 1 To UBound(Arr, 2)   '±N°ß¤@­Èªº¨ä¥¦Äæ¦ìªº­È¸Ë¤J¼Æ²Õ
                Arr(N, j) = Arr(i, j)
            Next
        End If
    Next
Range("A1").Resize(UBound(Arr), UBound(Arr, 2)) = ""    '²M°£­ì¸ê®Æ
Range("A1").Resize(N, UBound(Arr, 2)) = Arr             '¶K¤W°ß¤@­È¬ÛÃöªº­È
End Sub

samwang

¦^´_ 6# lilizzzz


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