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

[µo°Ý] (¤w¸Ñ¨M)´¡¤J¤@¦æªÅ¥Õ¦C¦³§Ö³tªº¤è¦¡¶Ü?

[µo°Ý] (¤w¸Ñ¨M)´¡¤J¤@¦æªÅ¥Õ¦C¦³§Ö³tªº¤è¦¡¶Ü?

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-4-24 15:35 ½s¿è

¥Ø«eª¾¹Dªº¤èªk¦³³o¤TºØ
¤£¹L«e­±¨âºØ³t«×¦n¹³µy·LºC
³Ì«á¤@ºØ¥Î»²§UÄ檺¤è¦¡¦n¹³¦³µy·L§Ö¤@ÂI
¤£ª¾¹DÁÙ¦³¨S¦³¤è¦¡¥i¥HÅý´¡¤J¤@¦æ³t«×§ó§Ö
½d¨Ò»¡©ú¡G·í½s¸¹¤£¤@¼Ë´¡¤J¤@¦æ°Ï¹j
  1.   Sub ´¡¤J¤@¦æ()
  2.     Application.ScreenUpdating = False
  3.     r = 4
  4.     Do While Cells(r, 3) <> ""
  5.     first = Cells(r, 3)
  6.     Do While Cells(r, 3) = first
  7.     r = r + 1
  8.     Loop
  9.     Rows(r).Insert
  10.     r = r + 1
  11.     Loop
  12. End Sub
½Æ»s¥N½X
  1. Sub ´¡¤J¦æ()
  2. Application.ScreenUpdating = False
  3. Dim ra As Long
  4. For ra = [A65536].End(xlUp).Row To 3 Step -1  '±q³Ì«á¤@¦æ¦V«e«Ø¥ß¦æ°j°é¡A¨B¶i-1
  5. If Range("C" & ra) <> Range("C" & ra - 1) Then
  6. Rows("" & ra).Insert shift:=xlDown    '´¡¤J1¦æ
  7. End If
  8. Next
  9. End Sub
½Æ»s¥N½X
  1. Sub ´¡¤J()
  2. Application.ScreenUpdating = False
  3. Dim ra As Long
  4. For ra = [A65536].End(xlUp).Row To 3 Step -1  '±q³Ì«á¤@¦æ¦V«e«Ø¥ß¦æ°j°é¡A¨B¶i-1
  5. If Range("C" & ra) = "Y" Then
  6. Rows("" & ra).Insert shift:=xlDown    '´¡¤J1¦æ
  7. End If
  8. Next
½Æ»s¥N½X
´¡¤J¤@¦æ.rar (28.46 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¦^´_ 1# freeffly
§Q¥Î¤p­p¥\¯à
  1. Sub Ex()
  2.    With Range("A1").CurrentRegion
  3.     .Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(2), _
  4.         Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  5.     .SpecialCells(xlCellTypeFormulas).EntireRow.Clear
  6.      Range("A1").ClearOutline
  7.     End With     
  8. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

­è­è¬O¤F¤@¤U±N©Ò¦³Y¿ï°_µM«á¤@¦¸´¡¤J¥i¥H´î¤Ö®É¶¡
¥i¬O¿ï¨úªº¤è¦¡­n°µ¨ì¸òctrl¿ï¨ú¤@¼Ë
¤U­±¬O¤§«e§Ú´£°Ý§Oªº°ÝÃD§ïªº
¥i¬O¥Î³oºØ¤è¦¡·|°µ¨ì³sÄò¿ï¨ú
§ï¦¨´¡¤J¦æ´N·|¸ò§Ú­nªº¤£¤@¼Ë
  1. Sub di()
  2.     Dim rag As Range, n As Integer
  3.    
  4.     n = 3
  5.     Do Until Cells(n, 2) = ""
  6.     If Cells(n, 3) = "Y" Then
  7.     If rag Is Nothing Then
  8.     Set rag = Cells(n, 3)
  9.     Else
  10.     Set rag = Union(rag, Cells(n, 3))
  11.     End If
  12.     End If
  13.     n = n + 1
  14.     Loop
  15.     rag.Select
  16.    End Sub
½Æ»s¥N½X
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-4-23 16:25 ½s¿è

¦^´_ 2# Hsieh


    ª©¥D§Aªº¤èªk¤§«e¨S·Q¹L(À³¸Ó¥i¥Hºâ¤£¥Îµ{¦¡½Xªº¤èªk§a)
   ¤£¹L­è­è¶]ÁÙ¬O¦³ÂI¤[<¤£¹L¤ñ­ì¨Ó¤è¦¡§Ö>   
     §Ú¦Û¤v¸Õ¿ý¥¨¶°¤]­nªá«Ü¦h®É¶¡(ÁÙ¨S¶]§¹)
    ­è­è·Qªº¥Î­Ó§O¿ï¨úµM«á¦A¤@°_´¡¤J¦n¹³¬O¥i¥H¤ñ¸û§Ö¹F¦¨
   ¤£¹L­Ó§O¿ï¨ú³o¦Uµ{¦¡½Xªº°Ê§@§Ú¤£ª¾¹D«çﻼg
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¥u­n¦³¬Û¾F¦C´NµLªk¤@°_¿ï¨ú¤À§O´¡¤Jªº¡A¨º·|´¡¤J¦b¦P¤@°Ï¶ô
¦A»¡§PÂ_´¡¤J¦C¬O«ÜºCªº¡A³Ì¦nªº¤è¦¡¬O­««Ø¸ê®Æ¡C¨Ò¦p¡G
  1. Sub ´¡¤J()
  2.     Dim rng, arr, i%, r%
  3.     T = Timer
  4.     rng = Range([a1], [b65536].End(3))
  5.     ReDim arr(1 To UBound(rng) * 2, 1 To 2) As String
  6.     For i = 1 To UBound(rng)
  7.         r = r + 1
  8.         If i > 2 Then
  9.             If rng(i, 2) <> arr(r - 1, 2) Then r = r + 1
  10.         End If
  11.         arr(r, 1) = rng(i, 1): arr(r, 2) = rng(i, 2)
  12.     Next
  13.     [a:b] = ""
  14.     [a1].Resize(r, 2) = arr
  15.     MsgBox Timer - T
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# oobird


    ª©¥D¦n§Ö³á
   ÁöµMµ{¦¡½X¬Ý°_¨Ó«Ü²³æ
   ¦ý¬O§Ú¬Ý¤£¤ÓÀ´
   redim¥H«áªº§Ú³£¬Ý¤£¤ÓÀ´
   ¥i¥H¤j²¤Á¿¤@¤U¶Ü
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD