Adding five new rows every 40 rows in a spreadsheet?
 

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...

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)


"Olzki" wrote in message
...
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...

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:


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)


"Olzki" wrote in message
...
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...

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)


"Olzki" wrote in message
...
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:


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)


"Olzki" wrote in message
...
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...

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

--
Dana DeLouis
Win XP & Office 2003


"Bob Phillips" wrote in message
...
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)


"Olzki" wrote in message
...
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:


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)


"Olzki" wrote in message
...
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...

Adding five new rows every 40 rows in a spreadsheet?
 

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

"Bob Phillips" wrote:


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)


"Olzki" wrote in message
...
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...

Adding five new rows every 40 rows in a spreadsheet?
 

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
wrote:

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

"Bob Phillips" wrote:


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)


"Olzki" wrote in message
...
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...

Adding five new rows every 40 rows in a spreadsheet?
 

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

"Gord Dibben" wrote:

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
wrote:

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

"Bob Phillips" wrote:


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)


"Olzki" wrote in message
...
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...

Adding five new rows every 40 rows in a spreadsheet?
 

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


On Thu, 17 May 2007 14:46:01 -0700, Excel Greenie
wrote:

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

"Gord Dibben" wrote:

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
wrote:

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

"Bob Phillips" wrote:


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)


"Olzki" wrote in message
...
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...