Insert a blank row after each record.
Posted by Mike on December 11, 2001 6:45 PM
I have a spreadsheet that contains 4500 records. I need to insert a blank row after each one of these records. If these blank rows can be inserted I also need to copy a formula into each of these blank rows. I would like to avoid doing this manually record by record.
Any suggestions are appreciated. Thanks
Posted by Bariloche on December 11, 2001 6:59 PM
Mike,
Modified as necessary, this should help you:
Sub InsertRowsAndFormula()
Dim i As Double
Dim LastRow As Double
LastRow = Cells(65536, 1).End(xlUp).Row
For i = LastRow To 2 Step -1
Rows(i).Insert Shift:=xlDown
Cells(i, 1).FormulaR1C1 = "=SUM(R[-2]C[4]:R[-1]C[8])"
Next i
End Sub
I just stuck a "dummy" formula in there to give you the idea of how that would be accomplished.
enjoy
Posted by Seleucus on December 11, 2001 7:41 PM
Mike,
This macro will, of course, do the job but is not very efficient since it has to loop though each cell, and repeat each time a row insert and a formula insert.
It can probably be done manually in about the same time it takes to run the macro (this should also be a useful learning process) :-
1. Select ColA
2. Insert>Columns
3. Put sequential mumbers in ColA from A1 to the last data row
4. Copy the range of sequential numbers and paste to the next blank cell in ColA
5. Select the entire rows covering all entries in ColA
6. Data>Sort>By ColA>No Header
7. You should now have blank rows inserted every other row
8. Delete ColA
9. Select ColA
10. Edit>GoTo>Special>Blanks
11. The blank cells in ColA should now be selected
12. Type in you formula and press Ctrl+Enter
A macro can be written based on the above steps, and which would be fast because it would not have to loop through all the cells.
This macro will, of course, do the job but is not very efficient.
It could be done manually in about the same time it takes to run the macro :-
1. Select ColA
2. Insert>Columns
3. Put sequential mumbers in ColA from A1 to the last data row
4. Copy the range of sequential numbers and paste to the next blank cell in ColA
5. Select the entire rows covering all entries in ColA
6. Data>Sort>By ColA>No Header
7. You should now have blank rows inserted every other row
8. Delete ColA
9. Select ColA
10. Edit>GoTo>Special>Blanks
11. The blank cells in ColA should now be selected
12. Type in you formula and press Ctrl+Enter
A macro could be written based on the above steps, and which would be fast because it would not have to loop through all the cells.
