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

[µo°Ý] VBA ²M°£¡BÁÙ­ì½Ð±Ð

[µo°Ý] VBA ²M°£¡BÁÙ­ì½Ð±Ð

½Ð°Ý¦U¦ì¤j¤j
¤p§Ì¦³°µ¤@­Ó­qÀ\ªí
¥B¦³³]¸m¤@­Ó²M°£«ö¶s
·í´«·s¤ë¥÷®É´N¥i¥H«ö²M°£«ö¶s¨Ó²M°£¤º®e

¦ý²{¦b¦³¤@­Ó°ÝÃD¬O
¦pªG¤£¤p¤ßÂI¨ì²M°£ªº«ö¶s
½Ð°Ý¥i¥HÁÙ­ì³Q²M°£ªº¤º®e¶Ü?


¤éµ{ªí 20230530_V01 °ÝÃD.rar (26.28 KB)

¦^´_ 1# ¬PªÅÉ@¦ÐÁl


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨ÒÀÉ
«á¾Ç«Øij
1.¦b²M°£«e¥ý½Æ»s¤@­Ó¬Û¦Pªº¤u§@ªí,©R¦W¬°"³Æ¥÷ªí"
2.ÁôÂ湳ƥ÷ªí
3.¦bÁÙ­ì®É§R°£²{ªí,Åã¥Ü³Æ¥÷ªí,§ó¦W¬°­ì²{ªí¦W
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483

ÁÂÁ«e½ú¦^´_
³oªº½T¬O­Ó¦n¤èªk

¦ý§Ú¥D­n¬O·Q­nÁקK¦pªG»~IJ²M°£«ö¶s®Éªº±¡ªp
©Ò¥H¤~°Ý¬Ý¬Ý¯à¤£¯àÁÙ­ì¸ê®Æ

TOP

¦^´_ 3# ¬PªÅÉ@¦ÐÁl


    ÁÂÁ«e½ú¦^´_
«á¾Ç«Øij¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Public CLearr, xR As Range
Sub ²M°£III()
Dim N1%, N2%
Set xR = [­qÀ\¤éµ{ªí!C6:AG38]
N1 = xR.Count
On Error GoTo Rear
N2 = xR.SpecialCells(2).Count
If N2 < (N1 \ 100) Then GoTo Rear
CLearr = xR: xR.ClearContents
Rear: End Sub
Sub ÁÙ­ìIII()
If Not xR Is Nothing Then xR = CLearr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# Andy2483
ÁÂÁ«e½ú¦^´_
´ú¸Õ¹L«á
ÁÙ­ì«ö¶s¥u¦³°¸º¸¦¨¥\¹L
½Ð°Ý«e½ú¯àÀ°¦£°£¿ù¶Ü?

TOP

¦^´_ 5# ¬PªÅÉ@¦ÐÁl


1.ÁÙ­ìIII ¥u°õ¦æ ²M°£III ªºÁÙ­ì
2.¨ä¥L°Ê§@ªºÁÙ­ì»Ý¨Ï¥ÎExcel ´_­ì¥\¯à
3.½Ð¤W¶Ç½d¨Ò
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# Andy2483
ÁÂÁ«e½ú¦^´_
§Ú´ú¸Õªº½T¬O¥ý²M°£«á«öÁÙ­ì
¦ý¥u¦³¦¨¥\ÁÙ­ì¤@¨â¦¸
¦A³Â·Ð«e½ú°£¿ù¡A·PÁÂ

¥H¤U¬°´ú¸ÕÀÉ
¤éµ{ªí 20230530_V01 °ÝÃD - µLªkÁÙ­ì.zip (29.02 KB)

TOP

¦^´_ 7# ¬PªÅÉ@¦ÐÁl

«ü©w¥¨¶°»Ý³]¬° ÁÙ­ìIII
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 8# Andy2483

ºÃ¡A§Ú¦³«ü©w¹L©ã@@
¤£¦n·N«ä¡A³o»ò²³æªº°ÝÃDÁÙ®ö¶O«e½ú±zªºÄ_¶Q®É¶¡¡C

TOP

¦^´_ 9# ¬PªÅÉ@¦ÐÁl


    ÁÂÁ«e½ú¦^´_

Option Explicit
Public CLearr, xR As Range
'¡ô«Å§i¥þ°ìÅܼÆ
'¡ô¦]¬°(CLearr,xR)³]¬°¥þ°ìÅܼƤSÅܼƨS¦³³QÄÀ©ñ±¼,
'©Ò¥H¥Î¦¹¤è¦¡ÁÙ­ì,¯ÂÄݶë÷´êªº,«D±M·~¤èªk,©ß¿j¤Þ¥É½Ð¦U¦ì«e½ú«ü±Ð

Sub ²M°£III()
Dim N1%, N2%
'¡ô«Å§iÅܼÆ
Set xR = [­qÀ\¤éµ{ªí!C6:AG38]
'¡ô¥OxRÅܼƬO [­qÀ\¤éµ{ªí!C6:AG38]Àx¦s®æ
N1 = xR.Count
'¡ô¥ON1ÅܼƬO Àx¦s®æ¼Æ¶q
On Error GoTo Rear
'¡ô¥Oµ{§Ç¹J¨ì¿ù»~®É´N¸õ¨ì¼Ð¥Ü Rear¦ì¸mÄ~Äò°õ¦æ
N2 = xR.SpecialCells(2).Count
'¡ô¥ON2ÅܼƬO xRÅܼƫDªÅ®æªº¼Æ¶q (¦pªG¨S¦³«DªÅ®æ·|¥X¿ù)
'¡ô¨¾¤î­«½Æ«ö ²M°£III

If N2 < (N1 \ 100) Then GoTo Rear
'¡ô¦pªG«DªÅ®æÀx¦s®æ¤Ö©ó1%? ¬O´N¸õ¨ì¼Ð¥Ü Rear¦ì¸mÄ~Äò°õ¦æ
'¡ôÁr´ú ²M°£III¥u·|¦b¤ë©³©Î¨C¤ë²Ä1¤Ñ¤~°õ¦æ,
'©Ò¥H¸ê®Æ¤Ó¤Ö¤£¯à²M°£

CLearr = xR: xR.ClearContents
'¡ô¦pªGµ{§Ç¦³¶]¨ì³o¸Ì´N¥O CLearrÅܼƬO ¤Gºû°}¦C,¥HxRÀx¦s®æ­È±a¤J,
'¡ô¥OxRÀx¦s®æ­È²M°£

Rear: End Sub

Sub ÁÙ­ìIII()
If Not xR Is Nothing Then xR = CLearr
'¡ô¦pªGxRÅܼƦ³ª«¥ó? ¬O´N¥OxRÀx¦s®æ¼g¤J CLearr°}¦C­È
'¦]¬°ÁÙ¨S¦³°õ¦æ ²M°£III ¤§«exR Is Nothing

End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD