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.

 

[wigi]

Why didn't you try:

Rows(21).Resize(8463).Insert

?

For beginners in VBA, I always advise to first read a book to get to know the syntax and the most common code structures.
the investment will pay off in the longer run.

 

[Redwolfx]

Or

Rows("21:8484").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

 

[DocAElstein]

Why didn't you try:

Rows(21).Resize(8463).Insert

?

For beginners in VBA, I always advise to first read a book to get to know the syntax and the most common code structures.
the investment will pay off in the longer run.

Hi Wigi

I’ve got about every book on VBA written and every VBA learn DVD that there is from about the last 10 years (In German!!). Also I’ve watched every YouTube VBA Video. I’ve been at least quickly through every book, and fell asleep many times to a VBA Video or YouTube Download!!. But for a beginner it’s a bit overwhelming. I almost got there!.. Your code suggestion was just what I wanted.

Sub RowsaddWigi()
   Rows(21).Resize(8463).Insert
End Sub 'RowsaddWigi()

Works (obvioulsy)
Thanks!! I almost got there with the Resize OR Insert idea…….
…… Resize OR insert seemed sensible. I just didn’t ( and still don’t quite) see the logic of resize.insert.. Maybe just the OOP Hierarchy stuff again..

……You resize it……… but then it just exists somewhere nowhere ……. so then you have to insert it. ?!? But then I have a problem with Object.Procedure.Procedure I was thinking that an object ( such as row) could have one or more procedures… but a procedure like resize having a procedure insert is confusing me still…
..Yeah Ok, I’ll keep reading and watching. Thanks for the benefit of your experience. I’ll try to give something back on the Forum later when I’ve learnt more!!!

 

[DocAElstein]

Or

Rows("21:8484").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Hi Redwolfx1
Thanks , Great. Both these work ( with 8483 not 8484)(I think I must have tried a variation on that somewhere but because of lack of experience I missed something or got the syntax a bit wrong)

Sub RowsAddRedwolfx1()
Rows("21:8483").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub 'RowsAddRedwolfx1()

Sub RowsAddRedwolfx2()
Rows("21:8483").Insert
End Sub [COLOR=#008000]' RowsAddRedwolfx2[/COLOR]()

I don’t quite see what the extra stuff in the first code does yet, but I’ll keep thinking about it!!!
Alan
Bavaria
Germany

 

[wigi]

I don’t quite see what the extra stuff in the first code does yet, but I’ll keep thinking about it!!!

If you do not provide a value for all of the arguments, VBA will use the default arguments for that method or make an educated guess.

 

[DocAElstein]

Hi.
. Can anyone tell me where I can find a good list of all arguments for procedures? (If possible with short explanations). None of the books I have show a complete list. (Or have I missed an obvious Help Function somewhere?). I have already asked around. The answer was usually some variant of “Don’t be stupid, that time is long gone – there are so many Functions and possibilities in VB and VBA that nobody knows them all!!”. Maybe I’m a bit old-fashioned, but that seem weird (and a bit scary!) to me?
Thanks
Alan

 

[wigi]

Did you ever use the Help function in VBA? (pressing F1)
Did you ever use the Object browser in VBA? (pressing F2)

 

[DocAElstein]

Did you ever use the Help function in VBA? (pressing F1)
Did you ever use the Object browser in VBA? (pressing F2)

Thanks again Zigi…..
. The F2 thing I know about and just about am getting to understand it (Excel object catalog , the OOP listing etc). -That I’ve read a lot about in the books.
(It pops up straight away in the VBA development environment, (what you get with Alt F11 etc etc. – in fact I have it permanently there most. With Windows Visa or XP I use the option to have 2 monitors at once:- my notebook screen permanently with development Environment and then the excel file on a big television screen just above.))
I am aware of the F1 as the general Excel Help thing. But I find it a bit overwhelming, and I’m looking but haven’t quite managed to get at what I want (For now, that is to say the Explanation of arguments for VBA Procedures)
. I know I ‘m missing the basic stuff, sorry to be a pain.. I’m just a bit old and bit more used to books and stuff. I tried Friday to enroll on a beginners course here, in Germany, but there may not be enough interest to let it Start. I have most books and learn videos as well. But as I said it is all a bit overwhelming,. From my involvement in this Forum I get the impression that even experienced users write there code a bit from “trial and error”. I find that strange.. My earlier Physics research was often trial and error, working at the forefront of technology, but I thought Computers and software should be an exact science. After all you’re just using Commands etc. that someone else wrote. But I guess it’s just getting a bit too big.
. But I’ll keep at it…
. Nice to get your reply… (I’m still a bit new to this Forum and thought, after the first few minutes the post gets lost as the Excel Forum moves so fast?!?)
Alan


P.s. Maybe I missed something again… Your Reply again:-

. “……Did you ever use the Help function in <acronym>VBA</acronym>? (pressing F1)
Did you ever use the Object browser in <acronym>VBA</acronym>? (pressing F2)….”. .

Object browser in <acronym>VBA</acronym>? (pressing F2):->>>>>>> Yeah I get that, the VBA Object browser by pressing F2
Help function in <acronym>VBA</acronym>? (pressing F1):->>>>>>>> No, I don’t quite get that. I just get a general Excel Help Window by pressing F1.

 

[Andrew Poulsom]

In this line of code:

Rows(21).Resize(8463).Insert

there are no objects, but two properties and a method. The Rows property returns a Range object. The Resize property is applied to that object and returns a (larger) Range object. Then the Insert method is applied to that object.

There are objects and properties that share the same word. For example the Range property returns a Range object. And there is a Workbooks object and a Workbooks property. The Workbooks object is a collection of open workbooks; and the Workbooks property returns that collection. When used with an index the Workbooks property returns a single workbook.