adding rows to spreadsheet

[janets]

How can I automatically enter a blank row every 6 rows in excel. doing it
manually is too time consuming because the spreadsheet is so large.

 

[Don Guillett]

sub addblankrowevery6()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 5 Step -6
Rows(i).Insert
Next i
End Sub

 

[ShaneDevenshire]

Hi,

1. You can do this manually as follows in an empty column enter the formula
=1/MOD(ROW(),6)
and copy it down as far as necessary. The rows with DIV/0 will be the ones
that Excel will Insert, so if they are on the wrong 6th row modify the
formula by subtracting for example
=1/(MOD(ROW()-1,6)) will put the DIV/0 on rows 1, 7, ... rather than 6, 12,
18...
2. Select the cells an copy and then choose Edit, Paste Special, Values
3. Press F5, Special, Constants and uncheck all but Errors, click OK
4. Press Ctrl + (Ctrl and the Plus key) and respond Entire Row.
5. Select the column where the formulas are and clear it.

The VBA code to do this is given below. To run this select a blank column
range, such as B1:B1000 and run the macro.

This macro will run about 50-100 times faster than a standard For Loop or Do
Loop macro.

Sub InsertRows()
Selection = "=1/MOD(ROW(),6)"
Selection = Selection.Value
Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert
Selection.EntireColumn.Delete
End Sub


If this helps, please click the Yes button.

 

[janets]

Don:

Thanks for taking the time to answer, but where do I enter this code?
and do I need to tell the subroutine how large the spreadsheet is so that
it knows when to stop?

 

[Don Guillett]

Just copy/paste into a module and fire it. You don't have to tell it how
large. Assumes column A.