Need to insert 9 rows every row

ang_99

New Member
Joined
Dec 19, 2005
Messages
2
I have a few thousand lines of data and I need to insert 9 rows after every row.

Can someone help me with the code.

Also, I have another sheet that I need to insert 1 row after ever 10 rows.

If you can help me with that also, that would be awsome.

I am a complete newbie to vb programming and any help is greatly appreciated.

If anyone can recommend a good book that does a nice job teaching a newbie, I'm listening....Thanks
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
suppose you have entries A1 to A9
(there may be more elegant solutions.)
run this sub and see wheher you get what you want.

Public Sub insert9rows()
Range("a2").Select
line1:
Range(ActiveCell, ActiveCell.Offset(9, 0)).Select
Selection.Insert shift:=xlDown
ActiveCell.Offset(11, 0).Select
If ActiveCell = "" Then Exit Sub


GoTo line1
End Sub
 

brycos

New Member
Joined
Dec 19, 2005
Messages
10
i would setup a loop using
Selection.EntireRow.Insert
OR
Selection.Insert Shift:=xlDown

Maybe something like:

Sub insert_9cells()
'
' Keyboard Shortcut: Ctrl+w
'
Do While IsEmpty(ActiveCell.Offset(1, 0)) = False
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

Loop
End Sub

I have not got this to work properly yet.
These links may help
http://www.exceltip.com/st/Using_Loops_in_VBA_in_Microsoft_Excel/628.html
http://www.vba-programmer.com/
 

ang_99

New Member
Joined
Dec 19, 2005
Messages
2
suppose you have entries A1 to A9
(there may be more elegant solutions.)
run this sub and see wheher you get what you want.

Public Sub insert9rows()
Range("a2").Select
line1:
Range(ActiveCell, ActiveCell.Offset(9, 0)).Select
Selection.Insert shift:=xlDown
ActiveCell.Offset(11, 0).Select
If ActiveCell = "" Then Exit Sub


GoTo line1
End Sub
_________________________________________________

The above is close,

only problem is its inserting rows in only the 1st column, I need it to insert an entire row across all columns.
 

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
ang_99 said:
I have a few thousand lines of data and I need to insert 9 rows after every row.

Can someone help me with the code.

Also, I have another sheet that I need to insert 1 row after ever 10 rows.

If you can help me with that also, that would be awsome.

I am a complete newbie to vb programming and any help is greatly appreciated.

If anyone can recommend a good book that does a nice job teaching a newbie, I'm listening....Thanks

For code for inserting the entire row, try recording a macro while you do this manually. You are after an EntireRow statement.

Perry
 

Watch MrExcel Video

Forum statistics

Threads
1,123,010
Messages
5,599,330
Members
414,305
Latest member
scarletX

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
Top