How to insert rows after each row of data (800 rows)?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jess - 23 Mar 2006 21:49 GMT

I need a spreadsheet with a blank row between each row of data. There are
800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

Toppers - 23 Mar 2006 21:49 GMT

Sub InsertBlankRow()
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
Rows(r).Insert Shift:=xlDown
Next r
end sub

> I need a spreadsheet with a blank row between each row of data. There are
> 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

bmorris - 31 May 2008 13:35 GMT

Toppers, your macro worked great. New problem. I have 2400 lines of data,
with a blank row every 4 or 5 rows. I need to insert an additional blank row
right after the existing one only, not after each row. Can you help?

> Sub InsertBlankRow()
> For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
[quoted text clipped - 4 lines]
> > I need a spreadsheet with a blank row between each row of data. There are
> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

Bernie Deitrick - 31 May 2008 19:53 GMT

bmorris,

Assuming that your filled/blank rows extend ot column A:

Sub InsertEvenMoreBlankRows()

Dim myA As Range

For Each myA In Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas
myA.Cells(myA.Rows.Count).EntireRow.Insert
Next myA

End Sub

HTH,
Bernie
MS Excel MVP

> Toppers, your macro worked great. New problem. I have 2400 lines of
> data,
[quoted text clipped - 11 lines]
>> > are
>> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

bmorris - 31 May 2008 21:16 GMT

Bernie,

I copied the macro and pasted just like this:

Sub InsertEvenMoreBlankRows()
Dim myA As Range
For Each myA In Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas
myA.Cells(myA.Rows.Count).EntireRow.Insert
Next myA
End Sub

The macro runs, but makes no changes. What I have is like this in Col A,
beginning at Row 1 and going down. No info is in Col B or beyond:

Row 1: data a
Row 2: data a
Row 3: data a
Row 4: BLANK
Row 5: data b

and so on. What i need to do it to add a blank row after Row 4, so there
are now (2) BLANK rows be between "data a" and "data b". Not all groups of
data (a - z) have (3) rows each; some groups have (5) or (6)....

Did I paste the macro incorrectly? Thanks for your help.

> bmorris,
>
[quoted text clipped - 29 lines]
> >> > are
> >> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

Rick Rothstein (MVP - VB) - 31 May 2008 21:50 GMT

It sounds like instead of blank data cells, you might have formulas in those
cells that evaluate to "" (the empty string, which is not a true "blank", at
least not to the SpecialCells function). If that is the case, I wonder,
then, if inserting a blank row (one with no formula) is really what you want
to do? Anyway, no matter what your situation is, this code might be useful
to you... it should double up single "blank" rows (either empty cells or
cells that evaluate to "") and leave multiple, contiguous "blank" rows alone
(that is, it will only duplicate isolated "blank" rows)...

Sub InsertEvenMoreBlankRows()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
If Len(.Cells(X - 1, "A").Value) > 0 And _
Len(.Cells(X, "A").Value) = 0 And _
Len(.Cells(X + 1, "A").Value) > 0 Then
.Cells(X, "A").Insert xlShiftDown
End If
Next
End With
End Sub

Rick

> Bernie,
>
[quoted text clipped - 58 lines]
>> >> > are
>> >> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

bmorris - 31 May 2008 21:46 GMT

Bernie,

i figured out the problem. my "blank" rows actually have a hidden character
in them. fixed it, your macro runs great. thank you very much.

> bmorris,
>
[quoted text clipped - 29 lines]
> >> > are
> >> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

Rebeca - 31 Jul 2008 21:59 GMT

I need a spreadsheet with a blank row between each row of data. There are
15,452 rows of data. I saw your answer below, but don't understand how or
what program I need? Can you please help?

Reply to this Message

Gord Dibben - 31 Jul 2008 23:17 GMT

First.............do you really need the inserted rows?

Perhaps just increasing row heights to double-height would suffice?

If not.................................

With a copy of your workbook open.

Alt + F11 to open Visual Basic Editor.

CTRL + r to open Project Explorer.

Right-click on your workbook/project and Insert>Module

Copy/paste the macro code into that module.

Alt + q to return to Excel window.

Tools>Macro>Macros. Select the macro and "Run"

If you have done it correctly, a row will be inserted between each of your
15,452 rows.

If you like it.......save as the original filename.

Gord Dibben MS Excel MVP

>I need a spreadsheet with a blank row between each row of data. There are
>15,452 rows of data. I saw your answer below, but don't understand how or
[quoted text clipped - 8 lines]
>> > I need a spreadsheet with a blank row between each row of data. There are
>> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

Rebeca - 01 Aug 2008 15:56 GMT

Thank you so much. It did work. I was able to add a row after each row of
data. It takes a little bit long (maybe 2 to 3 minutes), but it worked!

I also wanted to share with you that I was also able to add the rows by
doing the following:

>I went to this website http://www.asap-utilities.com/download-asap-utilities.php and downloaded the latest version of ASAP Utilities.

>I opened my excel spreadsheet (if your spreadsheet/workbook was open while you were installing the ASAP Utilities, you might need to close it and reopen it in order to see the ASAP Utilities on your the menu bar)

>ASAP Utilities>Columns & Rows>6. Insert in-between empty rows or columns

>Highlight or type in the range

>Insert rows

>Click on start

> First.............do you really need the inserted rows?
>
[quoted text clipped - 35 lines]
> >> > I need a spreadsheet with a blank row between each row of data. There are
> >> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message

Gord Dibben - 01 Aug 2008 20:05 GMT

Thanks for sharing.

I tried ASAP Utilities for a short time but then went back to using my own
customized add-in.

Gord

>Thank you so much. It did work. I was able to add a row after each row of
>data. It takes a little bit long (maybe 2 to 3 minutes), but it worked!
[quoted text clipped - 53 lines]
>> >> > I need a spreadsheet with a blank row between each row of data. There are
>> >> > 800 rows of data. Is there an easy way to accomplish this?

Reply to this Message