[µo°Ý] ¿ý»s«á°j°é³]©w¤Î´¡¤J½Æ»sªºÀx¦s®æ¬O§_¥i¥H¤£´¡¤J¹Ï®×

jackson7015

·Q½Ð°Ý¯¸¤Wªº«e½ú­Ì
§Ú¦³¤@¿ý»sªº¥¨¶°·Q°µInputBox¼Æ¶q°j°é
½Ð°Ý¸Ó¦p¦ó½s¼g

1. Sub ·s¼W¼Æ¶q()
   
2.      Rows("1:47").Select
   
3.     Selection.Copy
   
4.     Rows("48:48").Select
   
5.     Selection.Insert Shift:=xlDown
   
6.         Range("A4").Activate
   
7. End Sub

½Æ»s¥N½X

ÁÙ¦³§Ú¦b¤W­±ªº¥¨¶°¤¤¡A­t¨îªº½d³ò¦³¥]§t¨ì¹Ï¤ù¹Ï®×
¬O§_¦³¤£´¡¤J¹Ï¤ù¹Ï®×ªº¤è¦¡
¦]¬°ª½±µ±q48¦æ´¡¤J¡A´N¤£»Ý­n°µ¦æ¼Æ§P©w¡A¥i¥H¿ï¾Ü®æ¦¡³]©w

¥H¤W°ÝÃD¦A½Ð¤j¤jÀ°¦£¡A·PÁÂ~

jackyq

¦³µLª½±µªº«ü¥O?  ¤£¾å±o­C

¥Î´¡¤J«á¦A§â¹Ï§R°£ªº¤èªk

Sub kill_Image(R_«ü©w½d³ò As Range)

R_x1 = R_«ü©w½d³ò.Left
R_x2 = R_«ü©w½d³ò.Left + R_«ü©w½d³ò.Width
R_y1 = R_«ü©w½d³ò.Top
R_y2 = R_«ü©w½d³ò.Top + R_«ü©w½d³ò.Height

For Each p In R_«ü©w½d³ò.Parent.Pictures
    x1 = p.Left
    x2 = p.Left + p.Width
    y1 = p.Top
    y2 = p.Top + p.Height
   
    xx = x1 >= R_x1 And x1 <= R_x2 Or x2 >= R_x1 And x2 <= R_x2
    yy = y1 >= R_y1 And y1 <= R_y2 Or y2 >= R_y1 And x2 <= R_y2
  
    If xx And yy Then
       p.Delete
    End If
Next
End Sub

ML089

¦^´_ 1# jackson7015

Sub ·s¼W¼Æ¶q()
    Dim Rng As Range
    Set Rng = Rows("1:47")
    Rng.Copy
    With Rows("48:48").Resize(, Rng.Rows.Count)    'columns/rows
        .PasteSpecial Paste:=xlPasteFormats    '®æ¦¡
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats    '­È
        .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats    '¤½¦¡
    End With
End Sub

jackson7015

¦^´_ 2# jackyq
¦^´_ 3# ML089
·PÁÂjackyq¤ÎML089¦^ÂÐ

jackyq¤j¤jªº´ú¸Õ«á¥i¥H¨Ï¥Î¡A¦ý¬O»Ý­n¦h´X­Ó¨BÆJ¡A©Ò¥H¤§«á¦³Ãþ¦üªº»Ý¨D¥i¥H¦A¨Ï¥Î¡A·PÁÂ~

ML089¤j¤jªº¼gªk¡A¦n¹³µLªk±N®æ¦¡©Ò¦³½Æ»s¡A¦]¬°Àx¦s®æ¤º¤å¦r¤j¤p¤Î¸óÄæ¸m¤¤³£¨S¦³¥X²{
¦]¬°¦³³¡¤ÀÀx¦s®æ¤º¦³¶ñ¤JÃC¦â¡A¬O§_¤]¥i¥H¥H´¡¤Jªº¤è¦¡°µ³B²z?
ÁÂÁÂ~

jackyq

¦^´_ 4# jackson7015

¦]¬°  Excel  2000 ¨Sªk¥Î #3¼Óªº¤èªk

jackyq

¦^´_ 4# jackson7015

¤j¤j³o¼Ë¸Õ¸Õ¬Ý§a

........
    With Rows("48:48").Resize(, Rng.Rows.Count)    'columns/rows
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End With
Rows("48:48").PasteSpecial Paste:= 7
Rows("48:48").PasteSpecial Paste:= 13
Rows("48:48").PasteSpecial Paste:= .....  ' ³v¦¸²K¥[¯Ê¤Öªº
Rows("48:48").PasteSpecial Paste:= .....  ' ³v¦¸²K¥[¯Ê¤Öªº

°Ñ¼Æ¦Û¤v try
https://msdn.microsoft.com/en-us/library/bb241405%28v=office.12%29.aspx

jackson7015

¦^´_ 6# jackyq
·PÁÂjackyq¤j¤j¼ö¤ßªºÀ°¦£
7¡B13ªº½T¥i¥H¨Ï¥Î¡F©Ò¦³®æ¦¡³£¯à[¶K¤W]
¥B¤£·|±a¤J¹Ï§Î

¥i¬O¤p§Ì»Ý­nªº¥\¯à¬O¥H´¡¤J½Æ»sÀx¦s®æªº¤è¦¡
³o¼Ë¤£¥Î¦A§PÂ_­n¦h¤Ö®æ¦b[¶K¤W]
¦]¬°»Ý­nªº­¶¼Æ¨C¦¸¤£©w
©Ò¥H·Q­n¥ý¥X²{InputBox¸ß°Ý´¡¤J¦h¤Ö¦¸
µM«á°µ°j°é¦¡ªº­«Âд¡¤J§Y¥i

¦ý¬O¿ý»sªº®É­Ôµo²{[´¡¤J½Æ»sªºÀx¦s®æ]¤£¯à³sÄò°Ê§@
½Æ»s¤@¦¸¥u¯à´¡¤J¤@¦¸¡A´N»Ý­n¦A½Æ»s¤@¦¸¤~¯àÄ~Äò°Ê§@
©Ò¥H·Q½Ð±Ð¤j¤j¬O§_¦³¿ìªk°µ¥X³sÄò´¡¤JÀx¦s®æ(¤£­n¹Ï§Î)ªº¤è¦¡

¤£µM¥i¯à´N­n°µ§PÂ_¦æ¼Æ«á¡A¥H[¶K¤W]ªº¤è¦¡°µ³B²z¤F

¥H¤W¤£¾å±o·|¤£·|»¡±o¤Ó½ÆÂø
¦A½Ð«e½ú­Ì¬Ý¬Ý¡A·PÁÂ~

jackyq

¦^´_ 7# jackson7015

§Úª¾¹D§A¥Î insert ¬O¬°¤F¤è«K¤£¥Î­pºâ°Ï¶ô¤j¤p
¦ý insert ¦n¹³¨S¦³°Ñ¼ÆÅý§A¿ï¾Ü±Æ°£­þ¨Ç

    Sub ·s¼W¼Æ¶q()

    ´¡¤J¦¸¼Æ = 5
   
    Set R_Copy = Rows("1:47")
    Set R_Insert = Rows("48:48")
        
    For w = 1 To ´¡¤J¦¸¼Æ
    R_Copy.Select
    R_Copy.Copy
    R_Insert.Insert Shift:=xlDown
    Next
   
    Set R_Insert = R_Insert.Offset(-R_Copy.Rows.Count * ´¡¤J¦¸¼Æ)
    Set R_Insert = R_Insert.Resize(R_Copy.Rows.Count * ´¡¤J¦¸¼Æ)
    Call kill_Image(R_Insert)
End Sub


Sub kill_Image(ByVal R_«ü©w½d³ò As Range)


R_x1 = R_«ü©w½d³ò.Left
R_x2 = R_«ü©w½d³ò.Left + R_«ü©w½d³ò.Width
R_y1 = R_«ü©w½d³ò.Top
R_y2 = R_«ü©w½d³ò.Top + R_«ü©w½d³ò.Height

For Each p In R_«ü©w½d³ò.Parent.Pictures
    x1 = p.Left
    x2 = p.Left + p.Width
    y1 = p.Top
    y2 = p.Top + p.Height
   
    xx = x1 >= R_x1 And x1 <= R_x2 Or x2 >= R_x1 And x2 <= R_x2
    yy = y1 >= R_y1 And y1 <= R_y2 Or y2 >= R_y1 And y2 <= R_y2
  
    If xx And yy Then
       p.Delete
    End If
Next
End Sub

jackyq

¤èªk2

Sub ·s¼W¼Æ¶q_2()
    ´¡¤J¦¸¼Æ = 5
        
    Set R_Copy = Rows("1:47")
    Set R_Insert = Rows("48:48")
   
    Application.CutCopyMode = False
    R_Insert.Resize(´¡¤J¦¸¼Æ * R_Copy.Rows.Count).Insert
    R_Copy.Select
    R_Copy.Copy
    Set R_Insert = R_Insert.Offset(-R_Copy.Rows.Count * ´¡¤J¦¸¼Æ)
    For w = 1 To ´¡¤J¦¸¼Æ
        R_Insert.PasteSpecial
        Set R_Insert = R_Insert.Offset(R_Copy.Rows.Count)
    Next
End Sub

ML089

¦^´_ 4# jackson7015
³o¼Ë¸Õ¸Õ

Sub ·s¼W¼Æ¶q()
    Dim Rng As Range
    Application.DisplayAlerts = False
    Set Rng = Rows("1:47")
    Rng.Copy
    Rows("48:48").Resize(, Rng.Rows.Count).PasteSpecial Paste:=xlPasteAll
    Application.DisplayAlerts = True
End Sub