Add new Rows with VBA.

[DocAElstein]

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.

 

[Andrew Poulsom]

As I said I don't know why your Help system isn't working. is it set to Offline or Online?

http://www.mrexcel.com/forum/excel-...-visual-basic-applications-editor-2010-a.html

 

[Andrew Poulsom]

So in “Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 123 “ are we using the Range object or the Range Property of Worksheets("Sheet1") ... If it is the Range Property then it might explain why I need to initially select Cells(1 , 1) to avoid strange inconsistant errors sometimes.

We are using the Worksheet's Range property which returns a Range object (on that Worksheet). One reason that I can think of for needing to apply Cells(1, 1) to a Range object is that the Range's Value property can fail if the Range isn't a single cell (for example when testing it for equality).

 

[DocAElstein]

............Thanks for the link....... Configuring Help in VBA Editor 2010 ...." In the help page that opens (when you hit F1) in the bottom right corner can you see a button that probably says something like "Office Online" or some such value? Click this and change it to "Show Content From This Computer Only", then retry"

....That did the trick!!!!

 

[DocAElstein]

Thanks for all your Help
I'm very greatful
Alan