Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: insert rows automatically
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default insert rows automatically

    HiI have created a table & in column B I enter a number (i.e 3) is there a way to automatically add 2 rows below the line, so if the number was 5, it would add 4 rows below?RegardsStephen

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: insert rows automatically

    Try this:
    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified  9/13/2019  10:05:27 AM  EDT
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert Shift:=xlDown
    Application.EnableEvents = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: insert rows automatically

    Hi

    i followed your instructions and when i put the number 3 in column B, nothing happened?

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: insert rows automatically

    Well it works for me. I test all my scripts.
    Did you install the script as I explained.

    And you entered a number like 3 in column B

    And your workbook is Macro Enabled.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: insert rows automatically

    I di everything you mentioned

    here is the link to the file

    https://1drv.ms/x/s!Aq3WqOz73fYygYUnHWHhyScSho9udw?e=OCTc1u

    Regards
    Stephen

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: insert rows automatically

    I never click on links or open files posted here:

    We are talking about Column(2) of your worksheet.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: insert rows automatically

    yes, column B is where I put a number and when I tab across from it, nothing happens. So if I put 3 on line 50, I would want it to add two more lines below and then I can fill in the data and then on line 53 I may put the number 1, so not needing another line.

    04/08/2019 3 1 BRISTOL CITY A 3 1 HERNANDEZ
    BAMFORD
    HARRISON
    10/08/2019 1 1 NOTTINGHAM FOREST H 1 1 HERNANDEZ
    17/08/2019 2 0 WIGAN A 3 1 BAMFORD
    BAMFORD
    21/08/2019 1 0 BRENTFORD H 3 1 NKETIAH
    24/08/2019 3 0 STOKE CITY A 3 1 DALLAS
    ALIOSKI
    BAMFORD
    31/08/2019 0 1 SWANSEA H 0 1



    Last edited by sbrown64; Sep 13th, 2019 at 10:59 AM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: insert rows automatically

    Did you get any error messages?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: insert rows automatically

    I really do not know what to say.

    If you enter 4 into Range("B4") for example there should be 3 rows inserted below Row(4)

    This must be done manually not the result of a formula causing the change.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: insert rows automatically

    i saved your code to the worksheet page, not a mudule and when i go to the next line. i entered a date and then tabbed to column B and entered number 6 using the keyboard and nothing happened.

Some videos you may like

User Tag List

Tags for this Thread

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
  •