+ Reply to Thread
Results 1 to 3 of 3

Insert set number of rows between data and copy and paste data above empty rows

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Northern Ireland
    MS-Off Ver
    2013
    Posts
    8

    Insert set number of rows between data and copy and paste data above empty rows

    At the minute i use the code below to insert a set number of rows but now i need to be able to copy the data above the inserted blank rows to fill them.

    nRows = 23 'number of rows to insert
    nEvery = 1 'number of rows to skip between insertions
    i = nEvery + 1 'first row for insertion
    While Not IsEmpty(Cells(i, 1)) 'stop inserting when come to empty cell
    Rows(i & ":" & i + nRows - 1).Insert
    i = i + nRows + nEvery
    Wend
    End Sub

    Column A will have this years dates. I need to be able to insert 23 rows in between each date and fill the blanks with the date above the inserted rows.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: Insert set number of rows between data and copy and paste data above empty rows

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: Insert set number of rows between data and copy and paste data above empty rows

    Forgot to say; when you are inserting or deleting rows, it's usually easier to start at the bottom and work up.

+ 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. Copy cells with data - paste (insert rows) to next sheet.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2016, 01:28 PM
  2. [SOLVED] insert rows based on number in cell and copy the data down into the new rows
    By pziefle in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-05-2013, 11:19 AM
  3. [SOLVED] Insert Multiple Rows Based Off Number in Cell and Copy Data From Above New Rows
    By tstell1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2012, 04:15 PM
  4. Replies: 0
    Last Post: 05-18-2012, 11:18 PM
  5. Copy and insert rows n number of times + operations on data
    By rogerissimo in forum Excel General
    Replies: 2
    Last Post: 10-08-2010, 12:47 PM
  6. Copy and insert rows n number of times + operations on data
    By rogerissimo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2010, 11:35 AM
  7. Copy paste data, insert rows, insert data and change . to -
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2010, 01:44 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