Adding five new rows every 40 rows in a spreadsheet?

[Guest]

Hi,

I'm uppdating a huge spreadsheet and are in seroius need to figure out how
to add five new rows every fourtieth row. Advise anyone? Need to do this 400
times manually otherwise...

 

[Bob Phillips]

For i = 400 to step -40
Cells(i,"A").Resize(5,1).entirerow.insert
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thx a lot Bob.

You wouldn't also know how to program the same macro to "fill without
format" the five new rows from the last row above every time?

"Bob Phillips" skrev:

 

[Bob Phillips]

Not tested, but try

Dim i As Long
For i = 400 To 1 Step -40
Cells(i + 1, "A").Resize(5, 1).EntireRow.Insert
Cells(i, "A").EntireRow.Copy
Cells(i + 1, "A").Resize(5, 1).PasteSpecial Paste:=xlPasteValues
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

This is just a slight variation. I'm not sure how to do this without
copying some of the formatting, so this ideas just uses General Formatting.
You may need to adjust this.

Sub Demo()
Dim R As Long
Dim NRows As Long
Dim Inc As Long

Inc = 14
NRows = 5

For R = 100 To 1 Step -Inc
Rows(R).Resize(NRows).Insert
Rows(R).Offset(-1).Resize(NRows + 1).FillDown
Rows(R).Resize(NRows).NumberFormat = "General"
Next R
End Sub

 

[Guest]

I need to add a row every second row in spreadsheets that differ in size.
Some have 100 rows & some have 600 rows. What would the macro be

 

[Gord Dibben]

You should really make an attempt to create a new message rather than tagging
onto someone else's.

Having gotten that off my chest..............this macro looks at the used range
in column A on any worksheet and inserts a row after each existing row.

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 1
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 16 May 2007 19:55:00 -0700, Excel Greenie <Excel

 

[Guest]

Sorry about that. Thanks for the macro - works like a charm.

 

[Gord Dibben]

Thanks for the feedback.

The reason I mentioned not tagging onto an existing thread is the danger of your
post not being picked up if readers/responders have assumed that thread has been
successfully dealt with and have not put a "watch thread" on it or have chosen
to "ignore thread".


Gord