Any Excel bods

[mattvts23]

I have some data in a large spreadsheet (about 950 rows) Im wanting to add a new blank row under each one. Is there a quicker way rather than the insert option.

Thanks Matt

[saxoash]

easy, asuming all your data is on sheet 1 and it starts from cell A1.

On sheet 2:
In cell A1 do "=Sheet1!A1" and drag this cell across to what ever column your data goe sto on sheet one (so if you data is in calls A:G drag cell A1 on sheet 2 to G)

Now highlight all the data on sheet 2 (this will be A1:G1, if your data ends at column G) and select A2:G2 aswell (Assuming your data ends at column G)

And then just drag down to te bottom of the sheet. Then just copy all sheet 2 data and paste values.

Hope that helps lol.

If you need more help pm me

[mattvts23]

Tried that i must be doing it wrong...

[mattvts23]

could u do a quick spreadsheet and email me it

[saxoash]

Right Ignor that lol tis shit.

Open your Excel Workbook and press Alt + F11 to bring up VB screen.
In the Project window on the left hand side, right click in a blank area of that section and click insert Moduel, then double click on Moduel1.

A window should have apreared, now paste the following code in to it:

Sub InsertRowsAtIntervals()
'Written by Ashley Bowels
'27/04/207
'
Dim c As Range, i As Long, rwu As Long, rwl As Long
Dim rwc As Long, rwNo As Long, rwCount As Long, datarange As Long

datarange = InputBox("Please Enter The Row Number Your Data Ends At. ", "Range", 1)
Rows("1:" & datarange & "").Select
On Error Resume Next

If TypeName(Selection) <> "Range" Then Exit Sub
Set c = Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + Selection.Columns.Count - 1))
rwCount = Selection.Rows.Count
rwNo = InputBox("Enter Blank Row/Rows After How Many Rows?. ", "Insert Rows at Intervals", 1)
If Not IsNumeric(rwNo) Or rwNo < 1 Then Exit Sub
rwl = InputBox("How Many Blank Rows To Insert? ", "Insert Rows at Intervals", 1)
If Not IsNumeric(rwl) Or rwl < 1 Then Exit Sub
rwu = ActiveCell.Row + rwNo
rwc = rwl + rwNo
For i = 1 To Int(rwCount / rwNo)
Range(Cells(rwu, ActiveCell.Column), Cells(rwu + rwl - 1, ActiveCell.Column)).Select
Selection.EntireRow.Insert
rwu = rwu + rwc
Next
Range(c, Selection).Select
End Sub

Once thats done click anywhere in the code and then go:
>>RUN
>>Run Subs/UserForms

on the top menu.

You will get a few input boxes pop up follow the instructions and that should work.

The Message boxes:
1st: Enter the Row number where your data ends
2nd: Enter the number or rows betweek spaces (You want to enter 1)
3rd: Enter number of blank rows to insert (again you want 1)

Hope this helps.