Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 41

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

  1. #21
    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

    That code is not automatic & needs to be run manually. It will reset the event handler & should then mean that if you change a value in col B the other code should then work.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: insert rows automatically

    ok, so do i put that code in a module? then run it as a macro everytime i enter data or is there another way?

  3. #23
    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

    If you just put the cursor on the line that says
    Code:
    Sub Improve()
    and then press F5.
    Then go back to the workbook & enter a value of 2 or greater into col 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

  4. #24
    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 again, just followed your instructions and nothing happened?

  5. #25
    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

    You are entering the numbers into col B manually aren't you?
    - 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. #26
    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, i tab to column b enter a number and then press tab/enter to the next cell and nothing happens.

  7. #27
    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

    In that case I don't understand why it's not working for you. Especially as the original code works in the file you supplied.
    Try removing that code & using
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       Stop
       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:
       If Err.Number <> 0 Then MsgBox "Oops"
       Application.EnableEvents = True
    End Sub
    When you enter a number in col B the code window should open up with the "Stop" line highlighted in yellow.
    Does that happen?
    If so step through the code line by line using F8. What happens?
    - 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. #28
    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
    pressing F8 just tabs through the lines of code (each line being yellow) until the end, if i then go back to the sheet nothing has changed and if i press tab to a new line it then goes back to the code page.

  9. #29
    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 the part in red here get highlighted yellow
    Code:
    If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert
    The first part of that will get highlighted, but does the second part?
    - 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. #30
    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
    it all got highlighted

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
  •