Inserting rows

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top