Inserting numerous blank lines between specific rows of data

  • Thread starter Thread starter Deb
  • Start date Start date
D

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?
 
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
 
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
 
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?
 
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
 
Back
Top