ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[¤À¨É] §R°£«ü©w±ø¥ó¦C¸ê®Æ

[¤À¨É] §R°£«ü©w±ø¥ó¦C¸ê®Æ

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-14 09:38 ½s¿è

¦U¦ì«e½ú¦n
¤À¨É¦h¦~«e ­ã´£³¡ªL«e½ú«ü¾Éªº½d¨Ò,¸Ì­±¦³¦hºØ¤èªk,«á¾Ç¾q¶w,²{¦b¤~éÀ´
¶X¦¹¥DÃD¤À¨É°µ¾Ç²ß,ÁÂÁ½׾Â,ÁÂÁ«e½ú
Åwªï¦U¦ì¦b¥DÃD¸Ì¤@°_¾Ç²ß,¸Ì­±¦³°ò¥»ªº°}¦CÆ[©À,ÁÙ¦³«Ü¦h§Þ¥© !

20130802a01(§R°£«ü©w±ø¥ó¦C¸ê®Æ).rar (75.67 KB)

°õ¦æ«e:


°õ¦æµ²ªG:


Option Base 1
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/option-base-statement
Sub Option_Base_³¯­z¦¡_°ò©³´ú¸Õ()
    ReDim Arr(1)
    MsgBox LBound(Arr)
    '¡ô¥[¤F ³o¦æ Option Base 1 ,°}¦Cªº°_©l¯Á¤Þ¸¹·|¬O 1 (­ì¹w³]­È¬O 0)
End Sub
Sub ¥[ªk1()
    Dim Arr()
    '¡ô«Å§iArr¬OÁÙ¨S«Å§iºû«×ªº°}¦C
    ST = Timer
    '¡ô¥OST¬O ²{¦b®É¶¡
    With ActiveSheet
    '¡ô¥H¤U¬OÃö©ó²{¥Î¤u§@ªíªºµ{§Ç
        er = .UsedRange.Rows.Count
        '¡ô¥Oer¬O¼Æ¦r ¨Ï¥ÎªºÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òªº¦C¼Æ
        ec = 27
        '¡ô¥Oec¬O¼Æ¦r 27
        For r = 1 To er
        '¡ô³]¶¶°j°é!r±q1 ¨ì erÅܼÆ
            If Application.WorksheetFunction.CountIf(Range("A" & r & ":AA" & r), "EC1-") = 0 Then
            '¡ô¦pªG¥H WorksheetFunction.CountIf ¤èªk¦^¶Ç­È¬O 0 ??
            'AÄærÅܼƦCÀx¦s®æ¨ì AAÄærÅܼƦCÀx¦s®æ,¦¹½d³òÀx¦s®æ­È¬O "EC1-" ¦r¦êªº Àx¦s®æ¼Æ¶q
            'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheetfunction.countif

                N = N + 1
                '¡ô¥ONÅܼƲ֥[ 1
                ReDim Preserve Arr(27, N)
                '¡ô¥O°}¦CÂX¥RÄæ¼Æ¦ÜNÄæ!¨Ã«O¯d­ì°}¦C­È
                'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/redim-statement
                For c = 1 To 27
                '¡ô³]¶¶°j°é!c±q1 ¨ì 27
                    Arr(c, N) = .Cells(r, c).Value
                    '¡ô¥Oc¦CNÄæArr°}¦C­È¬O r¦CcÄæÀx¦s®æ­È
                Next c
            End If
        Next r
        .UsedRange.ClearContents
        '¡ô¥O¨Ï¥ÎªºÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òÀx¦s®æ²M°£¤º®e
        .[A1].Resize(N, 27) = Application.Transpose(Arr) 'Transpose ¶W¹LOFFICEª©¥»­­¨î¦C¼Æ·|¿ù»~
        '¡ô¥O[A1]ÂX®i¦V¤UN¦C,¦V¥k27Ä檺½d³òÀx¦s®æ­È¥HArr°}¦C­ÈÂà¸m«á­Ë¤J
    End With
    MsgBox Format(Timer - ST, "0.0¬í")
    '¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-14 10:09 ½s¿è

¥Î¦P¤@­Ó¤Gºû°}¦C³B²z:
1.¥ý©ñ¤J­ì©l¸ê®Æ
2.±N²Å¦X±ø¥óªº¸ê®Æ±q°}¦Cªº³Ì«e¤è³v¦C©ñ¤JÂл\±¼­ì©l¸ê®Æ
3.³Ì«áºë½Tªº±Nµ²ªG¸ê®Æ´£¥X©ñ¤JÀx¦s®æ¤¤



Sub ¥[ªk2()
    Dim Arr()
    '¡ô«Å§iArr¬OÁÙ¨S«Å§iºû«×ªº°}¦C
    ST = Timer
    '¡ô¥OST¬O ²{¦b®É¶¡
    With ActiveSheet
    '¡ô¥H¤U¬OÃö©ó²{¥Î¤u§@ªíªºµ{§Ç
        er = .UsedRange.Rows.Count
        '¡ô¥Oer¬O¼Æ¦r ¨Ï¥ÎªºÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òªº¦C¼Æ
        ReDim Arr(1 To er, 1 To 27)
        '¡ô«Å§iArr¬O¤Gºû°}¦C,½d³òÁa¦V±q1¨ì erÅܼƦC,¾î¦V±q1¨ì 27Äæ
        For r = 1 To er
        '¡ô³]¶¶°j°é!r±q1 ¨ì erÅܼÆ
            If Application.WorksheetFunction.CountIf(Range("A" & r & ":AA" & r), "EC1-") = 0 Then
            '¡ô¦pªG¥H WorksheetFunction.CountIf ¤èªk¦^¶Ç­È¬O 0 ??
            'AÄærÅܼƦCÀx¦s®æ¨ì AAÄærÅܼƦCÀx¦s®æ,¦¹½d³òÀx¦s®æ­È¬O "EC1-" ¦r¦êªº Àx¦s®æ¼Æ¶q

                N = N + 1
                '¡ô¥ONÅܼƲ֥[ 1
                For c = 1 To 27
                '¡ô³]¶¶°j°é!c±q1 ¨ì 27
                    Arr(N, c) = .Cells(r, c).Value
                    '¡ô¥ON¦CcÄæArr°}¦C­È¬O r¦CcÄæÀx¦s®æ­È
                Next c
            End If
        Next r
        .UsedRange.ClearContents
        '¡ô¥O¨Ï¥ÎªºÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òÀx¦s®æ²M°£¤º®e
        .[A1].Resize(N, 27) = Arr
        '¡ô¥O[A1]ÂX®i¦V¤UN¦C,¦V¥k27Ä檺½d³òÀx¦s®æ­È¥H Arr°}¦C­È­Ë¤J
    End With
    MsgBox Format(Timer - ST, "0.0¬í")
    '¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-15 08:49 ½s¿è

°f°j°é§R°£²Å¦X±ø¥óªº¦C

Option Base 1
'¡ô¥[¤F ³o¦æ Option Base 1 ,°}¦Cªº°_©l¯Á¤Þ¸¹·|¬O 1 (­ì¹w³]­È¬O 0)
Sub ´îªk1()
    ST = Timer
    '¡ô¥OST¬O ²{¦b®É¶¡
    Application.ScreenUpdating = False
    '¡ô¥O¿Ã¹õ¼È¤£ÀHµÛµ{§Ç°õ¦æ§@µ²ªGªºÅܤÆ
    er = ActiveSheet.UsedRange.Rows.Count
    '¡ô¥OerÅܼƬO ²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òªº¦C¼Æ
    'PS:¦pªG¤w¨Ï¥ÎÀx¦s®æ¤¤¶¡¦³ªÅ¦C/ªÅÄæ¹j¦¨¦h°Ï!UsedRange±N¥Lµø¬°³s±µªº¤@°Ï

    For i = er To 1 Step -1
    '¡ô³]°f°j°é!i±q erÅܼƨì 1,¨C¦¸°j°éÅýiÅÜ¼Æ -1
        If Application.CountIf(Rows(i), "EC1-*") > 0 Then Rows(i).Delete
        '¡ô¦pªG¥H WorksheetFunction.CountIf ¤èªk¦^¶Ç­È¬O > 0 !´N§R°£i°j°é¦C,¤U¤èªºÀx¦s®æ·|©¹¤W»¼¸É
        '¤èªk:AÄærÅܼƦCÀx¦s®æ¨ì AAÄærÅܼƦCÀx¦s®æ,¦¹½d³òÀx¦s®æ­È¬O ¶}ÀY¬°"EC1-" ¦r¦êªº Àx¦s®æ¼Æ¶q

    Next i
    MsgBox Format(Timer - ST, "0.0¬í")
    '¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub


Sub ´îªk2()
    ST = Timer
    '¡ô¥OST¬O ²{¦b®É¶¡
    Application.ScreenUpdating = False
    '¡ô¥O¿Ã¹õ¼È¤£ÀHµÛµ{§Ç°õ¦æ§@µ²ªGªºÅܤÆ
    er = ActiveSheet.UsedRange.Rows.Count
    '¡ô¥OerÅܼƬO ²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òªº¦C¼Æ
    'PS:¦pªG¤w¨Ï¥ÎÀx¦s®æ¤¤¶¡¦³ªÅ¦C/ªÅÄæ¹j¦¨¦h°Ï!UsedRange±N¥Lµø¬°³s±µªº¤@°Ï

    For i = er To 1 Step -1
    '¡ô³]°f°j°é!i±q erÅܼƨì 1,¨C¦¸°j°éÅýiÅÜ¼Æ -1
        If Not IsError(Application.Match("EC1-*", Rows(i), 0)) Then Rows(i).Delete
        '¦pªG¥HMatch()¤èªk§ä°j°é¦C¦UÀx¦s®æ¸Ì ¶}ÀY¬°"EC1-" ¦r¦ê,¦ÓMatch()¤£¬O¶Ç¦^#N/A ¿ù»~­È,
        '¦pªG±ø¥ó¦¨¥ß!´N§R°£i°j°é¦C,¤U¤èªºÀx¦s®æ·|©¹¤W»¼¸É
        'PS:¦pªG Match §ä¤£¨ì¬Û²Å±M®×¡A«h·|¶Ç¦^#N/A ¿ù»~­È¡C
        'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheetfunction.match
        'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/iserror-function

    Next i
    MsgBox Format(Timer - ST, "0.0¬í")
    '¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-15 09:42 ½s¿è

1.²Å¦X§R°£±ø¥óªº¦C,À³¥Î ¿ù»~­È¦r¦ê ¦b²Ä1Äæ´Ó¤J
2.À³¥Î Range.SpecialCells ¤èªk ,.Delete (3)§R°£¾ã¦C

Sub DelArray1()
Dim Arr, Brr, x&, Xm&, y&, Ym&, N&
'¡ô«Å§iÅܼÆ:(Arr,Brr)¬O ³q¥Î«¬ÅܼÆ,¨ä¥L¬Oªø¾ã¼ÆÅܼÆ
ST = Timer
'¡ô¥OST¬O ²{¦b®É¶¡
With ActiveSheet.UsedRange
'¡ô¥H¤U¬OÃö©ó²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òÀx¦s®æªºµ{§Ç
'PS:¦pªG¤w¨Ï¥ÎÀx¦s®æ¤¤¶¡¦³ªÅ¦C/ªÅÄæ¹j¦¨¦h°Ï!UsedRange±N¥Lµø¬°³s±µªº¤@°Ï

     Arr = .Value
     '¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H³oWithµ{§ÇÀx¦s®æ­È±a¤J
     Brr = .Columns(1).Value
     '¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H³oWithµ{§Ç¦a1ÄæÀx¦s®æ­È±a¤J
     Ym = UBound(Arr, 1)
     '¡ô¥OYm³oªø¾ã¼ÆÅܼƬOArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
     'PS: 1 ¥i¥H¬Ù²¤
     Xm = UBound(Arr, 2)
     '¡ô¥OXm³oªø¾ã¼ÆÅܼƬOArr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
     For y = 1 To Ym
     '¡ô³]¶¶°j°é!y±q1 ¨ì YmÅܼÆ
         For x = 1 To Xm
         '¡ô³]¶¶°j°é!x±q1 ¨ì XmÅܼÆ
             If InStr(Arr(y, x), "EC1-") Then Brr(y, 1) = "#N/A": Exit For
             '¡ô¦pªGy°j°é¦Cx°j°éÄæArr°}¦C­È¸Ì¥]§t¤F "EC1-"¦r¦ê!
             '´N¥Oy°j°é¦C²Ä1ÄæBrr°}¦C­È¬O "#N/A" ¦r¦ê,µM«áµ²§ô¦¹x¤º°j°é

         Next x
     Next y
     With .Columns(1)
     '¡ô¥H¤U¬OÃö©ó¥~Withµ{§ÇÀx¦s®æ²Ä1Ä檺µ{§Ç
          .Value = Brr
          '¡ô¥O­È¬O Brr°}¦C­È
          .SpecialCells(xlCellTypeConstants, 16).Delete (3)
         '¡ô¥O ¦³¿ù»~­Èªº³æ¤¸®æ©Ò¦bªº¦C§R°£
          'https://learn-microsoft-com.translate.goog/en-us/office/vba/api/excel.range.specialcells?_x_tr_sl=en&_x_tr_tl=zh-TW&_x_tr_hl=zh-TW&_x_tr_pto=sc
          'https://learn-microsoft-com.translate.goog/en-us/office/vba/api/excel.xlspecialcellsvalue?_x_tr_sl=en&_x_tr_tl=zh-TW&_x_tr_hl=zh-TW&_x_tr_pto=sc
          'https://learn-microsoft-com.translate.goog/en-us/office/vba/api/excel.xlcelltype?_x_tr_sl=en&_x_tr_tl=zh-TW&_x_tr_hl=zh-TW&_x_tr_pto=sc
          '.Delete (3): https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheet.rows

     End With
End With
MsgBox Format(Timer - ST, "0.0’")
'¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥Î¨â­Ó°}¦C³B¸Ì,¤@­Ó¸Ë­ì©l¸ê®Æ,¤@­Ó¸Ëµ²ªG¸ê®Æ

Sub DelArray2()
Dim Arr, Brr, x&, Xm&, y&, Ym&, N&
'¡ô«Å§iÅܼÆ:(Arr,Brr)¬O ³q¥Î«¬ÅܼÆ,¨ä¥L¬Oªø¾ã¼ÆÅܼÆ
ST = Timer
'¡ô¥OST¬O ²{¦b®É¶¡(¬í)
With ActiveSheet.UsedRange
'¡ô¥H¤U¬OÃö©ó²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òÀx¦s®æªºµ{§Ç
'PS:¦pªG¤w¨Ï¥ÎÀx¦s®æ¤¤¶¡¦³ªÅ¦C/ªÅÄæ¹j¦¨¦h°Ï!UsedRange±N¥Lµø¬°³s±µªº¤@°Ï

     Arr = .Value
     '¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H³oWithµ{§ÇÀx¦s®æ­È±a¤J
     Ym = UBound(Arr, 1)
     '¡ô¥OYm³oªø¾ã¼ÆÅܼƬOArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
     'PS:¥i¥H¬Ù²¤¬° Ym = UBound(Arr)

     Xm = UBound(Arr, 2)
     '¡ô¥OXm³oªø¾ã¼ÆÅܼƬOArr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
     .ClearContents
     '¡ô¥O²M°£Àx¦s®æ¸Ìªº¤º®e
     ReDim Brr(1 To Ym, 1 To Xm)
     '¡ô«Å§iBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,°}¦C¤j¤p:Áa¦V±q1¨ì YmÅܼÆ,¾î¦V±q1¨ì XmÅܼÆ
     For y = 1 To Ym
     '¡ô³]¶¶°j°é!y±q1¨ì YmÅܼÆ
         For x = 1 To Xm
         '¡ô³]¶¶°j°é!x±q1¨ì XmÅܼÆ
             If InStr(Arr(y, x), "EC1-") Then GoTo 101
             '¡ô¦pªGy°j°é¦Cx°j°éÄæArr°}¦C­È¸Ì¦³¥]§t "EC1-" ¦r¦ê!´N¸õ¨ì 101¦ì¸mÄ~Äò°õ¦æ
         Next x
         N = N + 1
         '¡ô¥ON³oªø¾ã¼ÆÅܼƲ֥[ 1
         For x = 1 To Xm: Brr(N, x) = Arr(y, x): Next x
         '¡ô³]¶¶°j°é!x±q1¨ì XmÅܼÆ: ¥ONÅܼƦCx°j°éÄæArr°}¦C­È¬O y°j°é¦Cx°j°éÄæArr°}¦C­È
101: Next y
     .Value = Brr
     '¡ô¥O³oActiveSheet.UsedRange¥HBrr°}¦C­È±a¤J
     '³oActiveSheet.UsedRange½d³ò¤ñBrr°}¦C¤j!¬°¤°»ò¤£·|²£¥Í¿ù»~®æ #N/A ??
     '¸g´ú¸Õªì¨B²z¸Ñ¨ä³W«h:
     '1.¶W¥X­ìActiveSheet.UsedRange½d³ò¤j¤p¤~·| ²£¥Í¿ù»~®æ #N/A
     '2.¦pªG°}¦C±a¤J°_©l®æ¤£¬O­ì¨Ó¦ì¸m,¤£¼vÅT¨äµ²ªG,¥H1.½d³ò¤j¤p¨M©w¨ä¬O§_²£¥Í¿ù»~®æ #N/A

End With
MsgBox Format(Timer - ST, "0.0’")
'¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

1.§Q¥Î2­Ó°}¦C¥[1»²§UÄ滲§U¯Á¤Þ¸¹:
1.1.±N²Å¦X§R°£±ø¥óªº¦C¦b»²§UÄæÅã¥ÜªÅ¦r¤¸
2.2.¤£²Å¦X§R°£±ø¥óªº¦C¦b»²§UÄæÅã¥Ü²Ö¥[¯Á¤Þ¸¹

2.¾ã­Ó°Ï°ì°µ»²§UÄ涶±Æ§Ç,±N²Å¦X§R°£±ø¥óªº¦CÀ½¨ì«á­±¥h

3.±N²Å¦X§R°£±ø¥óªº¦C¤@¦¸²M°£±¼

4.±N»²§UÄæ²M°£



Sub DelArray3()
Dim Arr, Brr(), xArea As Range, x&, Xm&, y&, Ym&, N&
'¡ô«Å§iÅܼÆ:Arr¬O ³q¥Î«¬ÅܼÆ,Brr¬O°}¦C,xArea¬OÀx¦s®æÅܼÆ,¨ä¥L¬Oªø¾ã¼ÆÅܼÆ
ST = Timer
'¡ô¥OST¬O ²{¦b®É¶¡(¬í)
With ActiveSheet.UsedRange
'¡ô¥H¤U¬OÃö©ó²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òÀx¦s®æªºµ{§Ç
     Arr = .Value
     '¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H³oWithµ{§ÇÀx¦s®æ­È±a¤J
     Ym = UBound(Arr, 1)
     '¡ô¥OYm³oªø¾ã¼ÆÅܼƬOArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
     'PS:¥i¥H¬Ù²¤¬° Ym = UBound(Arr)
     Xm = UBound(Arr, 2)
     '¡ô¥OXm³oªø¾ã¼ÆÅܼƬOArr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
     Set xArea = .Resize(Ym, Xm + 1)
     '¡ô¥OxArea³oÀx¦s®æÅܼƬO ¥H³oWithµ{§ÇÀx¦s®æÂX®i¦V¤UYmÅܼƦC,
     '¦V¥k(XmÅÜ¼Æ + 1)Äæ
     '(PS:¼W¥[1»²§UÄæ)

     ReDim Brr(1 To Ym, 0)
     '¡ô«Å§iBrr³o°}¦CÅܼƬO¤Gºûªº,
     '¤j¤p:Áa¦V±q1¨ìYmÅܼƦC,¾î¦V1Äæ,¯Á¤Þ¸¹¬O0¨ì0

     For y = 1 To Ym
     '¡ô³]¶¶°j°é!y±q1¨ìYmÅܼÆ
         For x = 1 To Xm
         '¡ô³]¶¶°j°é!x±q1¨ìXmÅܼÆ
             If InStr(Arr(y, x), "EC1-") Then GoTo 101
             '¡ô¦pªG¥HInStr()§PÂ_¦^¶Ç­È¤£¬O0,´N¸õ¨ì 101¦ì¸mÄ~Äò°õ¦æ
             'PS:§PÂ_:yÅܼƦC²ÄxÅܼÆÄæArr°}¦C­È¸Ì±q²Ä´X­Ó¦r¶}©l¥]§t "EC1-"¦r¦ê
             '¦pªG¤£¥]§t´N¦^¶Ç 0

         Next x
         N = N + 1: Brr(y, 0) = N
         '¡ô¥ON³oªø¾ã¼ÆÅܼƲ֥[ 1:¥OyÅܼƦC0¯Á¤Þ¸¹ÄæBrr°}¦C­È¬O NÅܼÆ
101: Next y
     If N = Ym Then Exit Sub
     '¡ô¦pªGNÅܼÆ=YmÅܼÆ!´Nµ²§ôµ{¦¡°õ¦æ(¥Nªí¨S¦³²Å¦X±ø¥óªº¦C)
     xArea.Columns(Xm + 1) = Brr
     '¡ô¥OxAreaÅܼƪº(XmÅܼÆ+1)Äæ½d³òÀx¦s®æ¥HBrr°}¦C­È±a¤J(PS:»²§UÄæ±a¤J»²§U¯Á¤Þ­È)
End With
With xArea
'¡ô¥H¤U¬OÃö©ó xAreaÅܼƪºµ{§Ç
     .Sort Key1:=.Item(Xm + 1), Order1:=xlAscending, Header:=xlNo
     '¡ô¥OxArea(Àx¦s®æ)¥H(XmÅܼÆ+1)Äæ²Ä1¦CÀx¦s®æ©Ò¦bªºÄæ(ABÄæ)·í°ò·Ç,
     '°µ¨S¦³¼ÐÃD¦Cªº¶¶±Æ§Ç±Æ§Ç

     .Rows(N + 1 & ":" & Ym).Clear
     '¡ô¥OxAreaÅܼƽd³ò¸Ìªº²Ä (NÅÜ¼Æ + 1)¦C¨ì ½d³ò¸ÌªºYmÅܼƦC,
     '³o½d³òÀx¦s®æ²M°£

     .Columns(Xm + 1).Clear
     '¡ô¥OxAreaÅܼƪº(XmÅܼÆ+1)Äæ½d³òÀx¦s®æ²M°£ (PS:»²§UÄæ²M°£)
End With
MsgBox Format(Timer - ST, "0.0’")
'¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-17 16:09 ½s¿è

²Å¦X§R°£±ø¥óªº¦C
1.¥ý¥Î ¿ù»~­È¦r¦ê¨ú¥N²Å¦X±ø¥óªºÀx¦s®æ­È
2.¦AÀ³¥Î Range.SpecialCells ¤èªk ,.Delete (3)§R°£¾ã¦C

Option Explicit
Sub ¨ú¥Nªk()
Dim ST
    ST = Timer
    '¡ô¥OST¬O ²{¦b®É¶¡(¬í)
    With ActiveSheet.UsedRange
    '¡ô¥H¤U¬OÃö©ó²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òÀx¦s®æªºµ{§Ç
        .Replace "EC1-", "#N/A"
        '¡ô¥OÀx¦s®æ­È¬O "EC1-"¦r¦êªº,³£±NÀx¦s®æ­È¸m´«¬°"#N/A"
        .SpecialCells(xlCellTypeConstants, 16).Delete (3)
        '¡ô¥O ¦³¿ù»~­Èªº³æ¤¸®æ©Ò¦bªº¦C§R°£
    End With
    MsgBox Format(Timer - ST, "0.0¬í")
    '¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

Option Explicit
Sub »²§UÄ椽¦¡()
Dim y&, N&, ST
'¡ô«Å§iÅܼÆ:(y,N)¬Oªø¾ã¼ÆÅܼÆ,ST¬O³q¥Î«¬ÅܼÆ
ST = Timer
'¡ô¥OST¬O ²{¦b®É¶¡(¬í)
y = ActiveSheet.UsedRange.Rows.Count
'¡ô¥Oy³oªø¾ã¼ÆÅܼƬO ²{ªí¤w¨Ï¥ÎÀx¦s®æÂX®i¬°³Ì¤p¤è¥¿½d³òªº¦C¼Æ
'PS:¦pªG¤w¨Ï¥ÎÀx¦s®æ¤¤¶¡¦³ªÅ¦C/ªÅÄæ¹j¦¨¦h°Ï!UsedRange±N¥Lµø¬°³s±µªº¤@°Ï

With [AB3].Resize(y)
'¡ô¥H¤U¬OÃö©ó[AB3]©¹¤UÂX®iyÅܼƦCªºµ{§Ç
     .Formula = "=IF(ISNA(MATCH(""EC1-*"",A3:AA3,0)),ROW(),""NA"")"
     '¡ô¥OÀx¦s®æ¤½¦¡¬O ¦pªG§ä¤£¨ì§k¦X±ø¥óªºÀx¦s®æ¼Æ´NÅã¥Ü¦C¼Æ,§_«h´NÅã¥Ü "NA"¦r¦ê
     .Value = .Value
     '¡ô¥O½d³òÀx¦s®æ­È¬O ¨äÅã¥Üªº¤½¦¡­È
     .Replace "NA", "", Lookat:=xlWhole
     '¡ô¥O½d³òÀx¦s®æ­È,¥þ§k¦X"NA"ªº´N¸m´«¬°ªÅ¥Õ
End With
[A3:AB3].Resize(y).Sort Key1:=[AB3], Order1:=xlAscending, Header:=xlNo
'¡ô¥O[A3:AB3]©¹¤UÂX®iy¦Cªº½d³ò¥H[AB3]¬°°ò·Ç°µ¨S¦³¼ÐÃD¦Cªº¶¶±Æ§Ç
N = [AB3].End(xlDown).Row
'¡ô¥ON¬O[AB3]©¹¤U´MªÅ¥Õ®æªº«e¤@¦C¦C¸¹
Rows(N + 1 & ":65536").Clear
'¡ô¥ON+1¦C¨ì65536¦C ²M°£
[AB:AB].Clear
'¡ô»²§UÄæ²M°£
MsgBox Format(Timer - ST, "0.0’")
'¡ô¸õ¥X´£¥Üµ¡ ¥H¤p¼ÆÂI1¦ìªº®æ¦¡Åã¥Ü°õ¦æªº¬í¼Æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD