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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

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,118,726
Messages
5,573,837
Members
412,552
Latest member
sfay
Top