Excel: Add new Rows with VBA.


Hello, I’m adding new / empty Rows (in this case 8463 Rows, starting at Row 21). The first code I got using the Macro recorder. (It simply adds rows one after the other). I modified it to reduce the steps by putting a simple loop in. It works but takes ages (even if I turn the screen off with Application.screenupdating = False ).


Sub Insert8463RowsAtRow21ByInserting8463Rows()
    Rows("21:21").Select
    For i = 1 To 8463 Step 1
    Selection.Insert Shift:=xlDown
    Next i
End Sub 'Insert8463RowsAtRow21ByInserting8463Rows()
The Second Code I got as well from the recorder. (It shifts everything down to make a space equal in Rows to the number of Rows I want.) That works quite quickly so I’m Happy. (I took the line Range("A8483").Activate out because I couldn’t see that it did anything. I hope that was OK??)
Sub Insert8463RowsAtRow21ByMovingEverything8463Downwards()
    Rows("21:8483").Select
    Selection.Copy
    Rows("8484:8484").Select
    ActiveSheet.Paste
    Rows("21:8483").Select
    '      Range("A8483").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub 'Insert8463RowsAtRow21ByMovingEverything8463Downwards()
But I am learning VBA and wanted to do it more professionally. I’ve tried a couple of hours to find a simple line that looks something like. “ Rows(“21:21”) . Add .AddRows insert Range( 21 21 : 8483 8483 ).Add Rows.Insert( ) etc. etc. “ But I haven’t found it yet. It’s probably obvious to a Profi. – can anyone help Thanks, Danke Alan Germany.


This question generated 24 answers. To proceed to the answers, click here.

This thread is current as of June 18, 2014.


For more resources for Microsoft Excel