- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
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 |
|