Insert Row, Shift Data Down

  • Hello,


    For the life of me, I cannot find or figure out the correct vba code for selecting a row and inserting one above it. I recorded a macro and got this code.


    Code
    1. Rows("9:9").Select
    2. Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove


    However, after running the macro, it still creates a row, but the data does not shift down. Any help is appreciated!

  • Re: Insert Row, Shift Data Down


    Not sure I follow you. When you run your recorded macro, it creates a new row 9 but the data in row 9 does not shift down?


    I tried:

    Code
    1. Rows(9).Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove


    with data in row 9 and a new row was added at 9 and the data was in row 10. Can you please provide more info on your issue.

  • Re: Insert Row, Shift Data Down


    Below is my macro...



    Scratch that. No row is created while this macro is run. This essentially takes data from sheet1 to sheet2. I want to merge certain data depending on the data on sheet1. When that happens, I want a new row to be created in order to make room for the new column of data.


    So, to be breif, I just need the command to work with my existing macro that will insert a new row under row 9.

  • Re: Insert Row, Shift Data Down


    Would it be possible for you to provide a mock workbook with made up data for sheet1 and the expected result in sheet 2? If you step through the macro what does it do at the insert rows line?

  • Re: Insert Row, Shift Data Down


    To step through the macro, press f8 in the vbe. I get a feel for what the macro is intended to do now, but I am still not certain on how the insert row comes into play.

  • Re: Insert Row, Shift Data Down


    I walked through the process. As I went step by step, it worked perfectly. However, when I ran the actual macro, it does not shift the data down.


    The insert of a new row is for data that needs to be placed under the 'Type' if there is matching data on Sheet1. However, if there is no matching data, I need to row to revert back to normal, so this is a muich bigger mess than I originally thought.

  • Re: Insert Row, Shift Data Down


    Ok, I think I may see now. You don't have the range attached to a sheet with the insert, so whatever sheet is active gets the action. If sheet1 is active you don't see it because there is no data in row 9. Try this variation.

  • Re: Insert Row, Shift Data Down


    That works perfectly! Thank you.


    Do you happen to have any ideas on how I could delete that extra row as well? There are two macros, one that doesn't add a row, and the one that you provided, which does. If I use the first macro after using the second, there will be an extra row... Any thoughts?


    Thank you.

  • Re: Insert Row, Shift Data Down


    Quote from JSpectrum;583185

    That works perfectly! Thank you.


    Do you happen to have any ideas on how I could delete that extra row as well? There are two macros, one that doesn't add a row, and the one that you provided, which does. If I use the first macro after using the second, there will be an extra row... Any thoughts?


    Thank you.


    I added a delete row command at the end of the second macro, so that solves my problem. However, how can I modify this so that it doesn't loop?

  • Re: Insert Row, Shift Data Down


    There are forum rules about multiple inquiries. Please start a new thread with your new question, and I will be happy to help.