Hi,
If you just need to do this once you can do the steps manually, but if you
might have to do it more than once, then the code below may be useful.
The following code inserts a row at every 100th line. To use it for any
other number of lines simply change the 100 in the first row of code to the
number you want.
To use it select a vacant range of cells in a single column corresponding to
the height of the range you want to insert blank rows iinto and run the
macro. Tools > Macro > Macros.
Sub InsertRows()
Selection = "=1/MOD(ROW(),100)"
Selection = Selection.Value
Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert
Selection.EntireColumn.Delete
End Sub
This code runs extremely fast.
To add this code to a workbook:
1. Press Alt+F11 (opens the Visual Basic Editor)
2. Select your file in the Project - VBA Project window, top left (press
Ctrl+R if you do not see it.)
3. Choose Insert > Module
4. Copy the code above to the module on the right
If this helps, please click the Yes button.
The manual step are:
1. Enter this formula on row 1
=1/MOD(ROW(),100)
2. Copy it down 5000 rows
3. Select all the formulas and copy them
4. With them selected choose Edit, Paste Special, Values
5. Press F5, Special, Constant, and uncheck all except Errors, click OK
6. Press Ctrl+- (control minus) and choose Entire row.
7. Clear the contents of the column where you put the formulas.
Shane Devenshire
