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

[µo°Ý] °õ¦æ³t«×¹LºC ¦p¦ó²¤Æ

[µo°Ý] °õ¦æ³t«×¹LºC ¦p¦ó²¤Æ

¥»©«³Ì«á¥Ñ lyc43210 ©ó 2015-4-12 17:36 ½s¿è

¤j®a¦n §Ú¬O­è¾Ç²ßvbaªº·s¤â
¦³°ÝÃD·Q½Ð±Ð

¦³¤@­Ó¤u§@ªí
°¸¼Æ¦Cªº¸ê®Æ­n²¾¨ì¤W­±©_¼Æ¦C«á­±¸É»ô
¤£¹L©_¼Æ¦C³Ì«á¤@®æ¤£¬O§¹¥þªÅªº ¦³­ÓªÅ¥Õ
¸É»ô«á¨C¦Cªºªø«×³£¬O¬Û¦Pªº

Ex.
[1234 ]
[123]
[123456 ]
[1]
     ¡õ
[1234123]
[1234561]

¥Ø«e§Ú¬O³o¼Ë¼g
  1. Sub hm()
  2.    
  3.     i% = 1
  4.    
  5.     Do
  6.         c = Cells(i, 1).End(xlToRight).Column
  7.         If c < 11 Then

  8.             a = Range(Cells(i + 1, 1), Cells(i + 1, 10))
  9.             Range(Cells(i, c), Cells(i, 11)) = a
  10.             Rows(i + 1).Delete
  11.         End If
  12.         
  13.         i = i + 1
  14.     Loop Until Cells(i, 1) = ""
  15.    
  16. End Sub
½Æ»s¥N½X
¯à°÷¹F¦¨§Ú·Q­nªº¥Øªº
¥i¬O°õ¦æ³t«×¤ÓºC¤F
¤u§@ªí¸ê®Æ¦³¤Q´X¸U¦C
§Ú°õ¦æ10¤ÀÄÁ¥u¯à§¹¦¨±Nªñ1/3
·Q½Ð±Ð¸Ó¦p¦ó²¤Æ ¨Ï³t«×¥[§Ö§¹¦¨

ÁÂÁ¤j®a

¤j®a¦n §Ú¬O­è¾Ç²ßvbaªº·s¤â
¦³°ÝÃD·Q½Ð±Ð

¦³¤@­Ó¤u§@ªí
°¸¼Æ¦Cªº¸ê®Æ­n²¾¨ì¤W­±©_¼Æ¦C«á­±¸É»ô
¤£¹L ...
lyc43210 µoªí©ó 2015-4-12 17:34

§â
a = Range(Cells(i + 1, 1), Cells(i + 1, 10))
Range(Cells(i, c), Cells(i, 11)) = a
§ï¦¨
Range(Cells(i + 1, 1), Cells(i + 1, 10)).Cut Cells(i, c + 1)
¥u¥Î c ¦n¹³¬O¤£¹ïªº, «Øij§ï¦¨ C + 1.
(±N·h²¾°Ê§@ª½±µ¥Ñ¤@­Ó¨ç¼Æ§¹¦¨)

¦A±N Range(Cells(i + 1, 1), Cells(i + 1, 10)).Cut Cells(i, c + 1)
§ï¦¨
Range(Cells(i + 1, 1), Cells(i + 1, 10-c)).Cut Cells(i, c + 1)
(¹ïÀx¦s®æ°µ°Ê§@·|¤ñ³æ¯Â­pºâ¼Æ¦r­nªá§ó¦h®É¶¡)

¦p¦¹,À³¸Ó·|¦³§ïµ½.
  1. Sub hm()
  2.    
  3.     Dim i%, c
  4.    
  5.     i = 1
  6.    
  7.     Do
  8.         c = Cells(i, 1).End(xlToRight).Column
  9.         If c < 11 Then

  10.             Range(Cells(i + 1, 1), Cells(i + 1, 10 - c)).Cut Cells(i, c + 1)
  11.             Rows(i + 1).Delete
  12.         End If
  13.         
  14.         i = i + 1
  15.     Loop Until Cells(i, 1) = ""
  16.    
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# luhpro

ÁÂÁ§A´£¨Ñªº«Øij
½T¹ê¦³Â²¤Æ¤F
¥i¬O¤£ª¾¬°¦ó °õ¦æªº³t«×¤Ï¦Ó§óºC¤F

ÁÙ¬O»¡
§Ú­ì¥ýªºÅÞ¿è·Qªk °õ¦æ³t«×¥»¨Ó´N¤£¨Î
­n¹F¨ì¦P¼Ë¥Øªº
¨ä¹ê¦³§ó¦nªº¼gªk¥i¥H¹ê§@

TOP

¦^´_  luhpro

ÁÂÁ§A´£¨Ñªº«Øij
½T¹ê¦³Â²¤Æ¤F
¥i¬O¤£ª¾¬°¦ó °õ¦æªº³t«×¤Ï¦Ó§óºC¤F

ÁÙ¬O»¡
§Ú­ì¥ý ...
lyc43210 µoªí©ó 2015-4-13 12:33

¶â?
Åܪº§óºC¤F¶Ü?

§Ú·Q§Ú¤j·§ª¾¹D­ì¦],
¦]¬°­pºâ¦¡Åܦh¤F,
­Y¸ê®Æµ§¼Æ¤£¦h«h®t²§¤£¤j,
§Aªº¸ê®Æ¶q¤Ó¤j·Pı´N·|«Ü©úÅã¤F.

¥i¥H¥ý¸ÕµÛ¨º§â Cut §ï¦¨ Copy ¸Õ¸Õ,
Cut·|¤ñCopy¦h¥X§â­ì¸ê®Æ²M°£ªº°Ê§@,
¦Ó³o¨ä¹ê¦bRows.delete´N¦³§â¾ã¦C§R±¼¤F,
©Ò¥H²M°£ªº°Ê§@¬O¥i¥H¬Ù²¤ªº.

¥t¥~¦h¥ÎÅܼƨú¥N¼W´îÅܼƭpºâ.
¦A«hµ½¥Î with ²¤Æ±q·½ÀY¶}©l¯Á¤Þª«¥óªº«ü¥O.
ÁÙ¦³ End(xlToRight) »P End(XlDown) ¦b¥u¦³ °_©lÀx¦s®æ¦³¸ê®Æ®É¥i¯à·|«ü¨ì³Ì«á¤@¦æ(¦C).
¤W­zÀ³¸Ó³£·|¦³®ÄªGªº,¸Õ¸Õ :
  1. Sub hm()
  2.    
  3.     Dim i%, c%, s%
  4.    
  5.     i = 1
  6.     s = Columns.Count
  7.    
  8.     Do
  9.         c = Cells(i, s).End(xlToLeft).Column
  10.         If c < 11 Then
  11.             i = i + 1
  12.             With Cells(i, 1)
  13.               Range(.Offset(0), .Offset(, 9)).Copy .Offset(-1, c)
  14.               .EntireRow.Delete
  15.             End With
  16.         End If
  17.         

  18.     Loop Until Cells(i, 1) = ""
  19.    
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# lyc43210
¸Õ¸Õ¬Ý
  1. Sub ex()
  2. Dim Ar() As String, i&, t$, s&
  3. For i = 1 To [A1].End(xlDown).Row Step 2
  4.   t = Trim(Cells(i, 1)) & Cells(i + 1, 1)
  5.    ReDim Preserve Ar(s)
  6.    Ar(s) = t
  7.    s = s + 1
  8. Next
  9. [B1].Resize(s, 1) = Application.Transpose(Ar)
  10. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2015-4-14 21:16 ½s¿è
¦^´_  lyc43210
¸Õ¸Õ¬Ý
Hsieh µoªí©ó 2015-4-14 10:54

³o­Óµ{¦¡ªº°õ¦æµ²ªG»P§Ú©Ò²z¸Ñ¨ìªº¼Ó¥D»Ý¨D¤£¤@¼Ë©O,
§Ú²q¼Ó¥D¬O­n©³¤U³o¼Ëªºµ²ªG:
(¼Ó¥Dªº³B²z¤º®e¤]¦³¥i¯à¬O¤å¦r, ¨Ò¤l¤¤¥þ¼Æ¦r¬O¬°¤F®e©ö²£¥Í½d¨Ò¸ê®Æ,¥D­n¬O²Ä¤G¦C¸ê®Æ·h¨ì²Ä¤@¦C¥ªÃä,·h²¾«á¨C¦C¸ê®Æ¼Æ¶q·|¬O¬Û¦Pªº.)

³B²z«e:

³B²z«e

³B²z«e.png
2015-4-14 21:13


³B²z«á:

³B²z«á

³B²z«á.png
2015-4-14 21:13

TOP

¶â?
Åܪº§óºC¤F¶Ü?

§Ú·Q§Ú¤j·§ª¾¹D­ì¦],
¦]¬°­pºâ¦¡Åܦh¤F,
­Y¸ê®Æµ§¼Æ¤£¦h«h®t²§¤£¤j,
§Aªº¸ê®Æ¶q ...
luhpro µoªí©ó 2015-4-13 22:00


ÁÂÁ§Aªº«Øij
¤£¹L³t«×³£¨S©úÅã´£¤É

§Ú¤]¸ÕµÛ·Ó§Aªº«Øij ¤@¦¸¤@¶µ­×§ï¹Á¸Õ
µo²{³t«×¦³¸û©úÅã®t²§ªº¼gªk ¤Ï¦Ó«o¬O
  1. a = Range(Cells(i + 1, 1), Cells(i + 1, 10))
  2. Range(Cells(i, c), Cells(i, 11)) = a
½Æ»s¥N½X
³o¼Ë¬Ý°_¨Ó¸û¤¾ªø
¥i¬O§Ú¤£ª¾¹D¬°¦ó «o·|¤ñcut©Îcopy¥u­n¤@¦æ´N°÷¤FÁÙ§Ö¤@¨Ç


-------
§Aªº²z¸Ñ¬O¹ïªº §Ú·Q¹F¦¨ªº¥Øªº´N¬O³o¼Ë
¤]·PÁÂHsieh¤j
°õ¦æ«Ü§Ö´N§¹¦¨¤F ¥i±¤¤£¬O§Ú­nªºµ²ªG

TOP

¥»©«³Ì«á¥Ñ bobomi ©ó 2015-4-15 14:38 ½s¿è

¤£¬O³Ì§Öªº¤èªk

    Sub hm()
      
        Application.ScreenUpdating = 0        
        i% = 1
        Do
            c = Cells(i, 1).End(xlToRight).Column
            If c < 11 And (i Mod 2) = 1 Then
                k = k + 1
               
                a = Range(Cells(i, 1), Cells(i, c))
                Range(Cells(k, 1), Cells(k, c - 1)) = a
               
                a = Range(Cells(i + 1, 1), Cells(i + 1, 10))
                Range(Cells(k, c), Cells(k, 11)) = a
            End If
            
            i = i + 1
        Loop Until Cells(i, 1) = ""
        
        Range(Cells(i, 1), Cells(k + 1, 1)).EntireRow.Delete
    End Sub

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2015-4-15 20:16 ½s¿è

¦^´_ 7# lyc43210
Delete/Copy ³o¨Ç°Ê§@³£¤ñ¸ûºC , ¸ê®Æ¶q¤j®ÉºÉ¶q¤Ö¥Î
  1. '°²³]¨C¦æ³Ì¤Ö³£¦³¤@µ§¸ê®Æ
  2. Sub Test()
  3.     Dim arSrc, arDes, m As Long, n As Long
  4.    
  5.     With ActiveSheet
  6.         arSrc = .[a1].CurrentRegion.Value
  7.         If UBound(arSrc) Mod 2 = 1 Then MsgBox "¸ê®Æ«D°¸¼Æ¦æ": Exit Sub
  8.         ReDim arDes(1 To UBound(arSrc) / 2, 1 To UBound(arSrc, 2))
  9.         
  10.         n = 1: m = 1
  11.         For i = 1 To UBound(arSrc)
  12.             For j = 1 To UBound(arSrc, 2)
  13.                 If arSrc(i, j) = "" Then
  14.                     Exit For
  15.                 Else
  16.                     If n > UBound(arDes, 2) Then ReDim Preserve arDes(1 To UBound(arDes), 1 To n)
  17.                     arDes(m, n) = arSrc(i, j)
  18.                     n = n + 1
  19.                 End If
  20.             Next
  21.             If i Mod 2 = 0 Then n = 1 : m = m + 1
  22.         Next
  23.     End With
  24.    
  25.     '³B²z§¹«áªº¸ê®Æ·s¼W¤u§@ªí¶K¤W
  26.     With Sheets.Add
  27.         .[a1].Resize(UBound(arDes), UBound(arDes, 2)).Value = arDes
  28.     End With
  29.    
  30. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 7# lyc43210
°}¦C³t«×·|¦n¨Ç
  1. Sub ex()
  2. Dim ar()
  3. x = 6 '«ü©w¾ã²z«áÄæ¼Æ
  4. With ActiveSheet
  5. Set Rng = .Columns("A:H").SpecialCells(xlCellTypeConstants)
  6. k = Application.CountA(Rng)
  7. s = Int(k / x) + IIf(k > Int(k / x) * x, 1, 0) '­pºâ°}¦C¦C¼Æ
  8. ReDim ar(s, 1 To x) '«Å§i°}¦C
  9. For Each a In Rng
  10. i = i + 1
  11. r = Int((i - 1) / x)
  12. j = i - r * x
  13.    ar(r, j) = a.Value '°}¦C½á­È
  14. Next
  15. .Columns("A:H") = "" '²MªÅ­ì¸ê®Æ
  16. .[A1].Resize(r + 1, x) = ar '¼g¤J°}¦C
  17. End With
  18. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD