Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 41

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

  1. #11
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: insert rows automatically

    Hi, sbrown64, I read your file.
    You need to explain that your data is in actual table & when you say you insert a number in column B you mean you always do it in last row of the table, right?
    So basically what you want is adding some rows at the bottom of the table?

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: insert rows automatically

    @sbrown64
    Could you please answer my question from post#8
    Cheers
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    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 Fluff, no error message.
    Akuini if you see the file, what I am doing is entering a football result, so I have a drop down on the end column so I can choose more names if needed. So if I enter 3 in column B then I want 2 extra lines, if I enter 4, I would want 3.

  4. #14
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: insert rows automatically

    Quote Originally Posted by sbrown64 View Post
    Hi Fluff, no error message.
    Akuini if you see the file, what I am doing is entering a football result, so I have a drop down on the end column so I can choose more names if needed. So if I enter 3 in column B then I want 2 extra lines, if I enter 4, I would want 3.
    In that case the code from My Aswer Is This works.
    I type 3 in B5 & it insert 2 blank rows below it.

    Is it possible that the Application.EnableEvents is turned off?
    Try running this code:
    Code:
    Sub toEvent()
    Application.EnableEvents = True
    End Sub
    then try typing a number in B5, then exit the cell.

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: insert rows automatically

    Another option is
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Value < 2 Then Exit Sub
       On Error GoTo Xit
       Application.EnableEvents = False
       If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert
    Xit:
       Application.EnableEvents = True
    End Sub
    This will prevent an error if you enter 1 into column B.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    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 Fluff

    I entered your code and entered 3 in column B line16, i then tabbed through to line19 and entered 4 and nothing happened?

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: insert rows automatically

    Have you tried running the code Akuini supplied in post#14?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #18
    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 have this code on the sheet1 page.

    Sub Improve()
    Application.EnableEvents = True
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value < 2 Then Exit Sub
    On Error GoTo Xit
    Application.EnableEvents = False
    If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert
    Xit:
    Application.EnableEvents = True
    End Sub

    Regards
    Stephen

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: insert rows automatically

    Did you run the code that that Akuini supplied in post#14?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #20
    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 it is included see post #18 at the top

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
  •