Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop for myRng for next set of 9 rows | Excel Programming | |||
insert rows and copying cells in column b,c, and d into new rows | Excel Programming | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
Insert page breaks every 50 rows but do not include hidden rows | Excel Programming | |||
range("myrng"), names("myrng").RefersToRange?? | Excel Programming |