Inserting rows

[Matt]

Can someone help me with some code that will insert 2 rows between a used range, e.g. if I have data in rows 2-100, how do i insert 2 rows between each of these rows?

Thanks

Matt

[Tom Urtis]

Here's code that will do that, and it's versatile because it involves an input box, so if you change your mind regarding how many rows to insert, just enter that number in the box.

Select the first cell in the range where you want to insert the rows, and run this macro.

Modify for column.

'''''''''''''''''''''''''

Sub AddRows()
Dim InsQuan As Integer
On Error Resume Next
InsQuan = InputBox("Enter number of rows to insert:", "Your Call")
If InsQuan <= 0 Then
MsgBox "Invalid number entered", 16, "Action cancelled."
Exit Sub
End If
Application.ScreenUpdating = False
Do Until Selection.Value = ""
ActiveCell.Offset(1, 0).Range("A1:A" & InsQuan).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(InsQuan, 0).Select
Loop
[A1].Select
Application.ScreenUpdating = True
End Sub

''''''''''''''''''''''''

Thanks to Mark W, who previously posted this non-VBA approach (though you asked for code in your question, this way is effective as well):

1. Number your rows (1 to 300 or whatever the last row is) in an unused column.
2. Paste a copy of these numbers (1 to 300 or whatever) directly beneath the copied numbers -- on unused rows. Do it again, because you want to insert 2 rows.
3. Sort (Ascending) on the column that contains the numbers, then clear the contents (the numbers) of that column.

[Matt]

Thanks Tom

[Amds]

On 2002-04-18 03:56, Tom Urtis wrote:
Thanks to Mark W, who previously posted this non-VBA approach (though you asked for code in your question, this way is effective as well):

1. Number your rows (1 to 300 or whatever the last row is) in an unused column.
2. Paste a copy of these numbers (1 to 300 or whatever) directly beneath the copied numbers -- on unused rows. Do it again, because you want to insert 2 rows.
3. Sort (Ascending) on the column that contains the numbers, then clear the contents (the numbers) of that column.

Also, a macro could be recorded by doing it this way, which would have the advantage of not needing any knowledge of VBA and perhaps having a faster run time.