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?

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?

"Toppers" wrote:

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

"Jess" wrote:

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,

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).Area s
myA.Cells(myA.Rows.Count).EntireRow.Insert
Next myA

End Sub

HTH,
Bernie
MS Excel MVP


"bmorris" wrote in message
...
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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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).Area s
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.

"Bernie Deitrick" wrote:

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).Area s
myA.Cells(myA.Rows.Count).EntireRow.Insert
Next myA

End Sub

HTH,
Bernie
MS Excel MVP


"bmorris" wrote in message
...
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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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.

"Bernie Deitrick" wrote:

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).Area s
myA.Cells(myA.Rows.Count).EntireRow.Insert
Next myA

End Sub

HTH,
Bernie
MS Excel MVP


"bmorris" wrote in message
...
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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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


"bmorris" wrote in message
...
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).Area s
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.

"Bernie Deitrick" wrote:

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).Area s
myA.Cells(myA.Rows.Count).EntireRow.Insert
Next myA

End Sub

HTH,
Bernie
MS Excel MVP


"bmorris" wrote in message
...
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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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 InsertModule

Copy/paste the macro code into that module.

Alt + q to return to Excel window.

ToolsMacroMacros. 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


On Thu, 31 Jul 2008 13:59:00 -0700, Rebeca
wrote:

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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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/downlo...-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 UtilitiesColumns & Rows6. Insert in-between empty rows or columns

Highlight or type in the range

Insert rows

Click on start

"Gord Dibben" wrote:

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 InsertModule

Copy/paste the macro code into that module.

Alt + q to return to Excel window.

ToolsMacroMacros. 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


On Thu, 31 Jul 2008 13:59:00 -0700, Rebeca
wrote:

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?

"Toppers" wrote:

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

"Jess" wrote:

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?

Thanks for sharing.

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


Gord

On Fri, 1 Aug 2008 07:56:00 -0700, Rebeca
wrote:

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/downlo...-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 UtilitiesColumns & Rows6. Insert in-between empty rows or columns

Highlight or type in the range

Insert rows

Click on start

"Gord Dibben" wrote:

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 InsertModule

Copy/paste the macro code into that module.

Alt + q to return to Excel window.

ToolsMacroMacros. 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


On Thu, 31 Jul 2008 13:59:00 -0700, Rebeca
wrote:

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?

"Toppers" wrote:

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

"Jess" wrote:

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?

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

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.
--
Regards
Roger Govier

"Linda" wrote in message
...
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