Inserting an empty row above every highlighted row of data

**terryhenderson** · 12-12-2014, 12:10 AM

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

**protonLeah** · 12-12-2014, 12:17 AM

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.

**cory0789** · 12-12-2014, 12:22 AM

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

Dim MyCell as Range
Dim lastRow as Long
lastRow = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

For i=2 to lastRow
MyCell = Rows(i).Select
If MyCell.Interior.ColorIndex > 0 Then
Selection.Insert Shift:=xlDown
Else
End If
Next i

**terryhenderson** · 12-12-2014, 01:42 AM

Sample data file attached as requested.
Many thanks

**protonLeah** · 12-12-2014, 02:22 AM

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.

Option Explicit

Sub InsertBlanks()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
    
    For i = lastRow To 2 Step -1
        Range(Cells(i, 1).Address).Select
        If Range(Cells(i, 1).Address).Interior.ColorIndex > 0 Then
            Range(Cells(i, 1).Address).EntireRow.Insert Shift:=xlDown
        End If
    Next i
End Sub

**terryhenderson** · 12-12-2014, 02:37 AM

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.

**protonLeah** · 12-12-2014, 02:41 AM

Glad to help

Inserting an empty row above every highlighted row of data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Inserting an empty row above every highlighted row of data

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

How to 'no-fill' empty cell that has been highlighted before?

Auto inserting empty rows into a populated sheet

Highlighted cell on next empty row in Column B after save

Locating the first empty cell in highlighted section

Inserting a empty Row if the value change

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions