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

½Ð°Ý¦p¦ó¿z¿ï¨C¤ÀÄÁ³N¾Ú

½Ð°Ý¦p¦ó¿z¿ï¨C¤ÀÄÁ³N¾Ú

A12520120706.rar (38.98 KB) ´N¬O§Ú¥u­n¤U­±ÀɮרC¤Àªº¼Æ¾Ú´N¦n
¥i¥HÀ°§Ú¶Ü

¦^´_ 1# xu123
  1. Option Explicit
  2. Sub Ex¶i¶¥¿z¿ï_¸ê®Æ½Æ»s¦b¨ä¥L¦a¤è()
  3.     With Sheets("A12520120706")
  4.        If .FilterMode Then .ShowAllData          '¸ê®Æ³B©ó¿z¿ï¼Ò¦¡ «hÅã¥Ü¥þ³¡¸ê®Æ
  5.         .Cells(1, .Columns.Count) = "XXX"        '¶i¶¥¿z¿ï: ­pºâ¦¡ ·Ç«hÄæ¦ì ¤£¥i»P¸ê®Æ®wÄæ¦ì¦WºÙ¤@¼Ë
  6.         .Cells(2, .Columns.Count) = "=SECOND(®É¶¡)=0"
  7.         .Range("L2").CurrentRegion = ""
  8.         .Range("A2:" & .Range("A2").End(xlToRight).End(xlDown).Address).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Cells(1, .Columns.Count).Resize(2), CopyToRange:=.Range("L2"), Unique:=False
  9.                                                'Action:=xlFilterCopy  '¸ê®Æ½Æ»s¦b¨ä¥L¦a¤è
  10.     End With
  11. End Sub
  12. Sub Ex¶i¶¥¿z¿ï_¦b­ì½d³òÅã¥Ü()
  13.     With Sheets("A12520120706")
  14.          If .FilterMode Then .ShowAllData
  15.         .Cells(1, .Columns.Count) = "XXX"
  16.         .Cells(2, .Columns.Count) = "=SECOND(®É¶¡)=0"
  17.         .Range("A2:" & .Range("A2").End(xlToRight).End(xlDown).Address).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Cells(1, .Columns.Count).Resize(2), Unique:=False
  18.                                                     'Action:=xlFilterInPlace   ¦b­ì½d³òÅã¥Ü
  19.     End With
  20. End Sub
½Æ»s¥N½X

TOP

ÁÂÁÂGBKEE ¤j¤j:P :P :P

TOP

¦^´_ 2# GBKEE
¦b°õ¦æ Ex¶i¶¥¿z¿ï_¦b­ì½d³òÅã¥Ü() «á¡A
¦p¦ó±N¦C¼Æ­«·s¨Ì§Ç¦Û°Ê±Æ¦C¡A §Y 1¡B2¡B3¡B7¡B15¡B19
­«·s½s¦C¬° 1¡B2¡B3¡B4¡B5¡B6¡C
ÁÂÁ±z¡I

TOP

¦^´_ 4# c_c_lai


   
play.gif
2012-7-28 19:06
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh
ÁÂÁ±zªº«ü¾É¡I
§Ú¬O·Q¨Ï¥Î VBA »yªkª½±µ³B²z¡A¤@®ð¨þ¦¨¡C
»¡¯u®æªº¡A§Ú¤£¤Ó·|¾Þ§@ Excel ªº¥\¯à¤¶­±¡A
¦A¦¸ÁÂÁ±z¯à§_«ü¾É§Ú¦p¦óÀ³¥Î VBA »yªk §¹¦¨¥¦¡H

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-7-28 19:41 ½s¿è

¦^´_ 6# c_c_lai
³o¥u¬O§i¶D§A¡A¥u­n¦b½s¸¹Äæ¦ì¨Ï¥Î¤½¦¡=SUBTOTAL(3,$B$2:B2)
¨Ó§¹¦¨½s¸¹«á
°õ¦æ¿z¿ï°Ê§@«á(¤£ºÞ¬O¤â°Ê©ÎVBA¾Þ§@)
½s¸¹³£¯à¦Û°Ê­«±ÆÅã¥Ü
¦pªG­n§â½s¸¹ªº¤½¦¡¤@¨Ö¼g¤JVBA
  1. Sub ex()
  2. With ActiveSheet
  3. If .[A1] <> "" Then  '§PÂ_¬O§_¤w¦s¦b½s¸¹Äæ¦ì
  4. .Columns("A").Insert  '´¡¤JAÄæ
  5. End If
  6. If .FilterMode Then .ShowAllData  '§PÂ_¬O§_¿z¿ïª¬ºA
  7. .[A2] = "½s¸¹"  '¼g¤JÄæ¦W
  8. .[A3].FormulaR1C1 = "=SUBTOTAL(3,R2C[1]:R[-1]C[1])"  '½s¸¹¤½¦¡
  9. .[A3].AutoFill .Range(.[A3], .[B65536].End(xlUp).Offset(, -1))  '¦V¤U¶ñº¡
  10. .[K2] = "=SECOND(B3)=0"   '¿z¿ï·Ç«h¤½¦¡
  11. .Range("A1").CurrentRegion.Offset(1).AdvancedFilter xlFilterInPlace, .[K1:K2]  '­ì¦a¶i¶¥¿z¿ï
  12. End With
  13. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh
ÁÂÁ±z¡I
¦ý¬O§Ú«üªº¬O¬õ®Ø¤ºªº±Æ§Ç¡G
10.png
2012-7-28 20:27

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-7-30 08:40 ½s¿è

¦^´_ 8# c_c_lai
­ì¦a¿z¿ï¤£·|§ïÅܦì¸m
¥u¬O±N¤£²Å¦X±ø¥óªº¸ê®Æ¦CÁôÂÃ
©Ò¥H¡A¤u§@ªíªº¦C¼ÐÅÒµLªk§ïÅÜ
¤]´N¬O»¡¿z¿ï¨Ã¤£·|Åý¸ê®Æ­«±Æ
¸ê®Æ­«±Æ¥²¶·¨Ï¥Î±Æ§Ç
play.gif
2012-7-30 08:40
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh
ÁA¸Ñ¤F¡AÁÂÁ±z¡I
10.png
2012-7-29 09:07

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD