Inserting numerous blank lines between specific rows of data
 

To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that?

Inserting numerous blank lines between specific rows of data
 

Try something like the following:

Sub AAA()
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
With WS
.Rows(221).Resize(8).Insert
.Rows(183).Resize(8).Insert
.Rows(145).Resize(8).Insert
.Rows(107).Resize(8).Insert
.Rows(69).Resize(8).Insert
.Rows(31).Resize(8).Insert
End With
End Sub

Note that it works from the bottom (highest row number) upward (to
lowest row numbers) and that the row number are 1 greater than the row
after which you want to insert the new rows.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Mon, 21 Dec 2009 13:28:01 -0800, Deb
wrote:

To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that?

Inserting numerous blank lines between specific rows of data
 

The best way to learn this stuff is to record the whole procedure...
Micky


"Deb" wrote:

To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that?

Inserting numerous blank lines between specific rows of data
 

This will work too. Select the cells in the first column of the range where
you want to insert the rows.

Sub EnterEightRows()
x = 0
For Each cell In Selection.Cells
x = x + 1
If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert
Next cell
End Sub

"Deb" wrote:

To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that?

Inserting numerous blank lines between specific rows of data
 

Thank you, Erin. This works fantastically. I can't thank you enough. It will
save me so much time when before I was doing this step manually.

Just curious, will this macro work for whatever length of data that I may
have, just run continuous until there is no more data to manipulate?

"Erin Searfoss" wrote:

This will work too. Select the cells in the first column of the range where
you want to insert the rows.

Sub EnterEightRows()
x = 0
For Each cell In Selection.Cells
x = x + 1
If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert
Next cell
End Sub

"Deb" wrote:

To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that?

Inserting numerous blank lines between specific rows of data
 

The posted macro requires you to pre-select the range of cells.

Revised to run to end of data in Column A.

Sub EnterEightRows()
Dim rng As Range
Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
x = 0
For Each cell In rng
x = x + 1
If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert
Next cell
End Sub


Gord Dibben MS Excel MVP

On Tue, 22 Dec 2009 07:58:04 -0800, Deb
wrote:

Thank you, Erin. This works fantastically. I can't thank you enough. It will
save me so much time when before I was doing this step manually.

Just curious, will this macro work for whatever length of data that I may
have, just run continuous until there is no more data to manipulate?

"Erin Searfoss" wrote:

This will work too. Select the cells in the first column of the range where
you want to insert the rows.

Sub EnterEightRows()
x = 0
For Each cell In Selection.Cells
x = x + 1
If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert
Next cell
End Sub

"Deb" wrote:

To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that?