Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 7th 18, 10:39 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 146
Default 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

  #2   Report Post  
Old August 7th 18, 10:45 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 146
Default 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.
  #3   Report Post  
Old August 7th 18, 10:58 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,604
Default 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
  #4   Report Post  
Old August 7th 18, 11:02 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 146
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop for myRng for next set of 9 rows L. Howard Excel Programming 10 June 16th 15 04:12 PM
insert rows and copying cells in column b,c, and d into new rows gbpg Excel Programming 0 December 31st 09 12:34 AM
Insert rows: Formats & formulas extended to additonal rows Twishlist Excel Worksheet Functions 0 October 22nd 07 04:23 AM
Insert page breaks every 50 rows but do not include hidden rows Martin[_21_] Excel Programming 5 March 12th 07 05:10 PM
range("myrng"), names("myrng").RefersToRange?? active_x[_8_] Excel Programming 2 November 8th 03 01:39 PM


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017