+ Reply to Thread
Results 1 to 12 of 12

Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Louisville, USA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Hi, I wanted to insert blank rows below and copy and paste the rows above to those blank rows when certain conditions are met. For example when you see a value 112 in column E then insert a blank row below it and copy and paste that same row above containing the value 112 onto the blank row you just created and also when you see 159 in column E perform this function above. I started building this out but ran into issues. Can someone please take a look? I will much appreciate this. Thanks

    Code

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Hi there,

    The fundamental problem occurs in the following line:

    Please Login or Register  to view this content.
    The equivalent line for z1 works because there are no blank cells in the range, but new rows will have been inserted before the above line of code is executed, and therefore z2 will refer to the cell immediately above the first newly-inserted row, rather than the last non-blank cell in the column.


    Try the following routine and see if it does what you want:

    Please Login or Register  to view this content.

    Defining the various parameters as Const(ants) keeps them in a convenient location in case they ever need to be changed in response to changes in worksheet layout etc.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Hi -

    Please try this one and let me know;
    Please Login or Register  to view this content.
    event

  4. #4
    Registered User
    Join Date
    01-15-2013
    Location
    Louisville, USA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Thanks Greg M, I ran your code it's almost perfect to what I was looking for, but just few questions. If I have multiple values in column E like 112, 159, 687 etc, how will I list those constants to achieve the same goal you did with just 112? And also it seems that Const iSTART_ROW As Integer = 2 has to be changed to Const iSTART_ROW As Integer = 1 to execute for row 2 is this correct? Thanks again

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Louisville, USA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Thanks event21, I ran your code and still digesting it. It seems when I have only few rows of data, it does not seem to work e.g. 4 rows of data. If I have many rows it works but only executes for the rest but not the first row of data in this case row 2 where the first value in column E is 112. I am still digesting your code though Lol.

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Hi -

    on this line
    Please Login or Register  to view this content.
    the number 5 is the first row of data, like in your file the row data starts in row # 5 so if your row data starts in row 1 or row 2 then change this line appropriately

    event

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Hi there,

    The following changes should do what you need:

    Please Login or Register  to view this content.

    Well done for noticing that the first row wasn't tested in the previous version of my code!

    iSTART_ROW is the number of the row from which testing should begin - I think that 5 is probably the correct value for your worksheet.

    These lines are included as a general "safety net", but are probably not required in this instance.

    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Another:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-01-2012
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    I would have done it like John. its quick , clean and simple
    I may not have use the copy function. I probably would have use

    Rows(i+1).insert
    rows(i+1).value = rows(i).value

    this way I can avoid the paste function, and i find it runs a bit quicker then copy past.

  10. #10
    Registered User
    Join Date
    01-15-2013
    Location
    Louisville, USA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Thank you Greg, I will run this and It looks like I might mark this thread resolved soon.

  11. #11
    Registered User
    Join Date
    01-15-2013
    Location
    Louisville, USA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Hi Steve7913/John, thank you for the comments I will ran the codes. This only gives me more options and a good learning experience of all the different ways of VBA programming.

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Insert Blank Rows and Copy Rows Above it with Data to These blank rows With Conditions

    Thanks, Pato - please continue to keep me informed.

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] insert blank rows every nth row and copy down data to fill blank rows
    By surpass in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-06-2014, 02:55 PM
  2. Replies: 4
    Last Post: 09-16-2014, 10:48 AM
  3. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  4. Copy up to blank cell, transpose, and insert rows without overwriting data below
    By scooter7 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-10-2013, 10:48 PM
  5. [SOLVED] Delete blank rows between data rows, shift rows up, then repeat
    By excelactuary in forum Excel General
    Replies: 2
    Last Post: 03-11-2013, 11:53 AM
  6. [SOLVED] Macro to insert blank rows so that the total number of rows with data is equal to 1021
    By nsm1411 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2013, 10:25 AM
  7. Replies: 1
    Last Post: 05-19-2011, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1