VBA Insert Rows - Really stuck

  • Morning, hopefully someone can help..I am a complete novice on VBA but have the below to insert rows based on a No in cell Q1...however the No in Q1 is generated with a formula and the insert rows doesnt work..If I manually add a No to Q1 it works fine...............help please




    Thanks in advance


    Steve

  • Hello Steve,


    The Worksheet_Change event code needs a User to physically (for want of a better word) make a change to the cell(s) in question so

    try using the Worksheet_Calculate event code instead:-


    Code
    1. Private Sub Worksheet_Calculate()
    2. Your code here
    3. End Sub

    You won't need to use "Target as Range" but instead reference the target cell directly.

    Here are some OzGrid tips:-


    https://www.ozgrid.com/Excel/f…BA1/excelvba1lesson16.htm


    I hope that this helps.


    Cheerio,

    vcoolio.

  • Hey Vcoolio, Thank you for coming back to me..I really am a newbie on this type of thing...so would it be as below


  • Hello Steve,


    More like this (I haven't tested it though)...........



    I hope that this helps.


    Cheerio,

    vcoolio.

  • Steve, please read the Forum Rules to understand how to use the Forum correctly. I have added code tags to your posts this time.


    You don't need to declare a variable for this. I don't understand the Case 1 line though


  • Hello Roy,


    Hope you're well and safe over there in the UK.

    Quote

    I don't understand the Case 1 line though

    I didn't actually read through Steve's code but that's an interesting query.

    Having now actually read the code, I'd assume that the second part of Case "0" isn't needed as well as Row9 is already hidden.

    I'll be interested to learn of Steve's reason.


    Cheerio,

    vcoolio.