Inserting Rows - Excel 2012

[rochedewet]

Hi all


This ia my fist time here.


Would like to find out how one would go about inserting "x" number of rows after "x" number of lines on a entire work sheet with about 2000 active line. See example below


BEFORE

text line 1

text line 2

text line 3

text line 4

text line 5

text line 6


AFTER

text line 1

text line 2

text line 3

text line 4

blank row 1

blank row 2

blank row 3

text line 5

text line 6

 

[bobhc]

Good day rochedewet

If you go to the left hand side of your spread sheet Click on no5 and while still holding down your left mouse button drag down to high light as many rows as you want new ones, then right click and from the drop down select insert......But remember the dangers!!if you have formulas or functions embedded in your work sheet inserting or deleting rows or columns could make a real mess of your data.

 

[shailyog]

it appears to me from the illustration given that you want to insert 3 rows after the text line 4. if it is as simple then you just need to select as much no. of existing rows after the text line 4 as much rows you want to insert and then press Ctrl and + together or right click and select insert.


if I misunderstood your question please reply.

 

[rochedewet]

Thanks for the reply it work like you said, but is there any way to do this through the entire sheet without selecting the lines everytime ?

 

[bobhc]

rochedewet if you want to do it through an entire sheet you need to tell something where to do it VBA?? you would need to tell VBA what to look for and then how many inserts, quite a bit of code. I just think that for the occasions you need to insert rows/columns the above method is quick enough

 

[SirJB7]

Hi, rochedewet!


Try copying and pasting this code into your worksheet windows pane code (Alt-F11 from Excel, double click on VBA project, Microsoft Excel Objects, then double click in the worksheet name). Then run the macro.


-----

[pre]

Option Explicit

Sub InsertXRowsAfterEachYRows()
' constants
Const kiTitleRows = 1
Const kiXRows = 9
Const kiYRows = 6
' declarations
Dim I As Long, J As Long, K As Long
' start
' process
With ActiveSheet
I = kiTitleRows
Do Until .Cells(I + 1, 1).Value = ""
I = I + kiXRows
Range(.Rows(I).Cells, .Rows(I + kiYRows - 1)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
I = I + kiYRows
Loop
End With
'end
Beep
End Sub

[/pre]
-----


Adjust the three constants to your proper values.


Regards!