Need to insert 9 rows every row

[ang_99]

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

 

[venkat1926]

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]

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]

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]

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

 

[daniels012]

The best thing to do is download andrew's utilities. At this website:
http://www.andrewsexceltips.com/my_utilities.htm
It is awesome. Sooooo many useful tools.

Michael