How to quickly insert a blank row every 5 rows?

M

Med

I have a worksheet that has about 2000 rows. I want to insert a blank
row every five rows. Doing the same steps 400 times is time consuming
and no fun. And I have to do this often. Is there a quicker way to
do this?
 
C

Chip Pearson

You can use a VBA macro like the following:


Sub AAA()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = 6 To LastRow Step 6
Rows(RowNdx).Insert
Next RowNdx
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
H

Herbert Seidenberg

Or without VBA:
Insert an adjacent helper column,
enter the label Seq on top and then
sequential numbers. Name numbers Seq.
Type the label Alpha into a cell.
Enter this formula into the cell below Alpha:
=MOD(Seq,4)=0 (FALSE response is OK)
Select both cells and name them Criteria.
Use Insert > Name > Define.
Select your data range, including Seq and
all top labels, and name it Database.
Select the first empty cell at the bottom of
Database and name it Extract.
Data > Filter > Advanced Filter >
Copy to another location
Type Database, Criteria and Extract into the
three range boxes respectively. Click OK.
Format the appended data with a unique format,
say red font.
Select Database plus the appended data
and sort it by Seq.
Edit > Replace > Format > Choose Format from Cell >
Click on a red cell > Replace All
 
Top