VBA Macro. Possible to add 100 Rows vs 1?

[Bluechip12]

This Macro only adds one row at a time. Is it possible to add 100 rows at one time?


Any help would be greatly appreciated.


Sub Insert_Row_Input_Actual()

' Insert Macro

'

Dim r As Integer

r = Range("a65536").End(xlUp).Row


Range("a" & r + 1).Select

Rows(ActiveCell.Row).Select

Selection.Insert Shift:=xlDown

Rows(ActiveCell.Row - 1).Select

Selection.Copy

Rows(ActiveCell.Row + 1).Select

ActiveSheet.Paste

Range("a" & r + 1).Select

Range(ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 7)).Select

Selection.ClearContents

End Sub

 

[Luke M]

Certainly. It depends on how many rows you have selected before you do the insert. I believe this is the best translation/condensing of your macro.

[pre]

Sub Insert_Row_Input_Actual()
' Insert Macro
'
Dim r As Integer
'Determine the last row
r = Range("a65536").End(xlUp).Row

'Copy the last row
Cells(r, "A").EntireRow.Copy

'Paste the last row 100 times after last row
Cells(r + 1, "A").Resize(100, 1).EntireRow.Insert Shift:=xlDown

'Clear contents of D:H in new rows
Range(Cells(r + 1, "D"), Cells(r + 100, "H")).ClearContents
End Sub

[/pre]

 

[Bluechip12]

Luke,


Thank you so much. You saved my weekend.


Have a great weekend.


Cheers.

 

[Luke M]

Glad I could help. Enjoy the free time. =)