Inserting numerous blank lines between specific rows of data

[Deb]

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?

 

[Chip Pearson]

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]

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Erin Searfoss]

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]

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?

 

[Gord Dibben]

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