Automatically insert blank row between header and predefined row when row deleted

  • I have a sample spreadsheet.


    I need a macro that inserts a blank row between the header row and the predfined count row when a row is deleted. It should be inserted after the last row of data.


    I have very limited knowledge of macros and usually google my way through but Im stuck on how to do this.


    Example.


    If row 2 in the spreasheet was to be deleted by the user then I need a macro that can either:


    1/ Adds a new blank row automatically or


    2/ Can add a row if the user presses a button.


    3/ Must add the new blank row after the row that still contains data.


    Any assistance is greatly appreciatted.


    Cheers

    Files

    • Test.xlsx

      (11.69 kB, downloaded 45 times, last: )
  • Re: Automatically insert blank row between header and predefined row when row deleted


    Try the attached.


    This is the code in the Worksheet Object Module:
    Option Explicit
    Dim s As String



    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Automatically insert blank row between header and predefined row when row deleted


    Hi KjBox


    Thank you for your reply.


    I am using Excel 2010.


    I put the code into the XLSM worksheet that I am working on (in the Objects).


    I deleted a row to test it. A new row wasn't inserted.


    I am a basic user of macros. All I did was copy paste the code you suplied.


    Kind regards

  • Re: Automatically insert blank row between header and predefined row when row deleted


    In that case I suspect there is a fundamental difference between your actual workbook and the sample workbook you supplied. Can you attach your actual workbook, if an of the data is sensitive then replace that data with dummy data of the same type.


    Also please show our desired result on a separate sheet.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Automatically insert blank row between header and predefined row when row deleted


    Hi KjBox


    Thank you for your reply.


    I have sent you a PM.


    Kind Regards