+ Reply to Thread
Results 1 to 15 of 15

Create a Macro To Add a New Row

  1. #1
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Create a Macro To Add a New Row

    Hello,

    I would like to create a macro that can add a new row after the last cell. For example, the current row with data is A5, I want to add a new row after A5 is A6, another new row after A6 is A7 and so on. The script below keep adding a blank row right after row 5 instead of one-off. Would anyone be able to fix this? Thank you in advance for your help!

    With ActiveSheet
    LR = 5
    .Rows(LR).Insert Shift:=xlDown
    .Rows(LR - 1).Copy
    .Rows(LR).PasteSpecial xlFormats
    End With
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Create a Macro To Add a New Row

    Please note that, to comply with forum rules, you should use Code Tags around your code. The Mods can, rightly, pick you up on this and give you an infraction if they are in the frame of mind to do so. When you get to more than 10 posts, that's normally a given as you are expected to know and comply with the rules.

    Anyway ..

    Replace:

    Please Login or Register  to view this content.

    With:

    Please Login or Register  to view this content.
    Strictly speaking, as it's the last row anyway, there's not much point *inserting* a row. You could, I think, leave out the .Insert line.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Create a Macro To Add a New Row

    BTW, Forum rules say to add code tags around any posted code.(helps people read/understand the postings easier)

    Please Login or Register  to view this content.

    *whoops see post above, i was too slow replying
    Last edited by jwright650; 04-11-2013 at 10:45 AM. Reason: too slow typing
    Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
    John Wright

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Create a Macro To Add a New Row

    I might have misunderstood ... I thought you were adding at the end.

    If it's in the middle of data, maybe:

    Please Login or Register  to view this content.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro To Add a New Row

    TMS,

    Your script is perfect if I know a place (Activecell) where i want to add new row. But if I accidently leave the active cell at wrong location, it will at a new row where I dont want to. In my case, I want to add a new row in 'Work Products' area after row 10. I want the macro to add a new row after row 10 is 11, another row after 11 is 12 and so on. My macro keep adding a new row right after row 10, because I declared 'LR = 10'. How can I fix this? I'm totally new to VBA.

    Please see attachment for details.

    Thanks!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro To Add a New Row

    I forgot to mention the macro 'NewWorkProduct' is in Module 2 and this is where I want to add new row.

  7. #7
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Create a Macro To Add a New Row

    What is the criteria that determines the location of the new row to be added?

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Create a Macro To Add a New Row

    You could use Application.WorksheetFunction.Match to determine the row number of the section heading: "Review Issue Metrics" and go up a row from there and insert your new line.


    Regards, TMS

  9. #9
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro To Add a New Row

    Just copy the previous row format such as colors, size, front to new row but clear the content in it. New row to be add must be after row 10 and continue with one-off.

  10. #10
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro To Add a New Row

    I don't know how to use this function. Would you please be able to show me?

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Create a Macro To Add a New Row

    Hi Beginner,

    Here's a method I often use to find a column or row - you should get used to the Option Explicit, it will save you many headaches - so good luck!

    Please Login or Register  to view this content.
    Last edited by xladept; 04-11-2013 at 01:58 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Create a Macro To Add a New Row

    Please Login or Register  to view this content.

    Regards, TMS

  13. #13
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro To Add a New Row

    xladept,

    It works perfectly. Thanks a bunch for your help!

    Kind regards,

  14. #14
    Registered User
    Join Date
    04-03-2013
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro To Add a New Row

    Thank you all for your help! I really appreciated!

    Best Regards,

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Create a Macro To Add a New Row

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1