Excel VBA插入复制的单元格语法

Question

我的宏的目标太简单，只复制其中包含值1的任何行的单元格，并将相同的值插入右上方的另一行中。唯一需要注意的是我在列B & C中也有值，所以我希望复制和插入这些值。我的宏是低于

我已经认识到，问题是优秀的是采取列AR相对于范围AR，但我有点麻烦绕过这一点，因为我不能想到命名我的R变量另一种方式绕过这个问题。

非常感谢您的帮助！

Sub BlankLine() 

    Dim Col As Variant 
    Dim BlankRows As Long 
    Dim LastRow As Long 
    Dim R As Long 
    Dim StartRow As Long 

    Col = "A" 
    StartRow = 1 
    BlankRows = 1 

    LastRow = Cells(Rows.Count, Col).End(xlUp).Row 

    Application.ScreenUpdating = False 

    With ActiveSheet 
     For R = LastRow To StartRow + 1 Step -1 
      If .Cells(R, Col) = "1" Then 
       .Range("AR:CR").Copy 
       Selection.Insert Shift:=xlDown 
      End If 
     Next R 
    End With 
    Application.ScreenUpdating = True 

End Sub 

Answer 1

更新时间：我改变了代码，只是复制这些三列（A至C），然后插入新行，然后设置的值，以这三个列。评论在代码中。如果要复制多个列，只是改变了整在dupRange变量（即，在.Cells(R, Col + 2))的）

Sub BlankLines() 

Dim Col As Integer 
Dim BlankRows As Long 
Dim LastRow As Long 
Dim R As Long 
Dim StartRow As Long 

Col = 1 'changed to number 
StartRow = 2 'changed to 2 since you have a header 
BlankRows = 1 'not used in this code... 

LastRow = Cells(Rows.Count, Col).End(xlUp).Row 

Application.ScreenUpdating = False 

With ActiveSheet 
    For R = LastRow To StartRow Step -1 
     If .Cells(R, Col) = "1" Then 
      Dim dupRange As Variant 'array to store values 
      Set dupRange = .Range(.Cells(R, Col), .Cells(R, Col + 2)) 'store the column values for the row 
      .Rows(R & ":" & R).Insert Shift:=xlDown 'insert the new row 
      c = 0 'to increment over the copied columns 
      For Each v In dupRange 
       .Cells(R, Col + c) = v 'sets the array values to each column 
       c = c + 1 
      Next v 
     End If 
    Next R 
End With 

Application.ScreenUpdating = True 

End Sub 

试试这个。不是100％确定这是否是你想要的。