+ Reply to Thread
Results 1 to 7 of 7

Inserting an empty row above every highlighted row of data

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Inserting an empty row above every highlighted row of data

    Hi Guys,

    I have an Excel worksheet with over 20,000 rows of data. Certain rows of data are highlighted in yellow. Each highlighted row represents the beginning of the chunk of data in the following rows until the next highlighted row. The number of rows in each chunk of data is variable.

    What I need to do is to insert an empty row ABOVE each highlighted row, so that each chunk of data is separated from the one above and under it; and this will enable me for a further manipulation of each chunk of data on its own.

    Can anyone give a highly appreciated assistance in this matter?

    Many thanks in advance

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,467

    Re: Inserting an empty row above every highlighted row of data

    Attach a Workbook
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"

    You only need post a few sample blocks of records not 20K rows.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    04-14-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Inserting an empty row above every highlighted row of data

    Try this out... not sure if it will work or not

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Inserting an empty row above every highlighted row of data

    Sample data file attached as requested.
    Many thanks
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,467

    Re: Inserting an empty row above every highlighted row of data

    This code starts at the bottom because if you start at the top, the new last cell is pushed down by each insertion below the initial last cell and some will be missed.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Inserting an empty row above every highlighted row of data

    Thank you very much protonLeah for your genius code. It ran smoothly and quickly as it did exactly the required task. Always ahead protonLeah, once again thank you very much.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,467

    Re: Inserting an empty row above every highlighted row of data

    Glad to help

+ 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. How to 'no-fill' empty cell that has been highlighted before?
    By airbatucampur92 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2014, 12:17 AM
  2. Auto inserting empty rows into a populated sheet
    By Backroomgeeza in forum Excel General
    Replies: 4
    Last Post: 04-24-2012, 08:09 PM
  3. Highlighted cell on next empty row in Column B after save
    By OZover in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-04-2010, 03:39 PM
  4. Locating the first empty cell in highlighted section
    By Tommanley in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-11-2009, 08:40 AM
  5. Inserting a empty Row if the value change
    By kaffal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2009, 01:36 PM

Tags for this Thread

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