Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Add new Rows with VBA.

  1. #1
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ).

    Code:
    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??)


    Code:
    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.
    Last edited by DocAElstein; Jun 18th, 2014 at 05:03 PM.

  2. #2
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    Why didn't you try:

    Code:
    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.
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  3. #3
    Board Regular
    Join Date
    Feb 2013
    Location
    USA
    Posts
    1,161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    Or

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


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  4. #4
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    Quote Originally Posted by wigi View Post
    Why didn't you try:

    Code:
    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.

    Code:
    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!!!

  5. #5
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    Quote Originally Posted by Redwolfx View Post
    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)

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

    Code:
    Sub RowsAddRedwolfx2()
    Rows("21:8483").Insert
    End Sub ' RowsAddRedwolfx2()
    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

  6. #6
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    Quote Originally Posted by DocAElstein View Post
    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.
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  7. #7
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    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

  8. #8
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

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

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  9. #9
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add new Rows with VBA.

    Quote Originally Posted by wigi View Post
    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 VBA? (pressing F1)
    Did you ever use the Object browser in VBA? (pressing F2)….”.
    .

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

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add new Rows with VBA.

    In this line of code:

    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.
    Microsoft MVP - Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •