For each c in myRng insert 6 rows
 

Hi team

As the title suggests. I have days spanning from row 2 through to 270.

Rather than manually inserting 6 rows for each date, I was hoping someone could assist with something a tad quicker please.

Essentially something loke:

For each c in myRng

For each c in myRng insert 6 rows
 

No idea why it posted when I wasn't finished, but! here we go again:

For each c in myRng
If Not c = "" then
with c
..Resize(6).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
..offset(7, 0).select
End with
End if
Next c

The insert works, but then it would go skewiff on the next part of the statement offsetting the focus 7 rows down to find the next c in the loop.

As always, many thanks in advance.
Cheers
Mark.

For each c in myRng insert 6 rows
 

Hi Mark,

Am Tue, 7 Aug 2018 02:45:51 -0700 (PDT) schrieb Living the Dream:

No idea why it posted when I wasn't finished, but! here we go again:

For each c in myRng
If Not c = "" then
with c
.Resize(6).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.offset(7, 0).select
End with
End if
Next c

try:

Sub InsertRows()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LRow To 2 Step -1
.Rows(i).Resize(6).Insert
.Range(.Cells(i, 1), .Cells(i + 5, 1)).Value = .Cells(i + 6, 1).Value
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

For each c in myRng insert 6 rows
 

On Tuesday, August 7, 2018 at 7:58:51 PM UTC+10, Claus Busch wrote:
Hi Mark,

Am Tue, 7 Aug 2018 02:45:51 -0700 (PDT) schrieb Living the Dream:

No idea why it posted when I wasn't finished, but! here we go again:

For each c in myRng
If Not c = "" then
with c
.Resize(6).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.offset(7, 0).select
End with
End if
Next c

try:

Sub InsertRows()
Dim LRow As Long, i As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LRow To 2 Step -1
.Rows(i).Resize(6).Insert
.Range(.Cells(i, 1), .Cells(i + 5, 1)).Value = .Cells(i + 6, 1).Value
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus

As always, ever reliable and supremely quick with a handing code.

That worked a treat, thank you.

Cheers
Mark.