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

[Guest]

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?

 

[bmorris]

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?

 

[Bernie Deitrick]

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

 

[bmorris]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rebeca]

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?

 

[Gord Dibben]

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

 

[Rebeca]

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

 

[Gord Dibben]

Thanks for sharing.

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


Gord

 

[Linda]

I need a solution for deleting a row (with data) after every row of data
(>1000 rows). However, I can't adapt the macro above to a acceptable outcome.
If anybody can help?

regards, Linda

 

[Roger Govier]

Hi Linda

you didn't post your code.
Your question is rather ambiguous the header says about Inserting rows, the
body talks about Deleting rows.
Can you explain a little more clearly what you have now, and what you want
to end up with.