Inserting rows after every week

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
I have a list of consecutive dates running down column A in dd/mm/yy format. I am trying to insert 2 rows after every Sunday. My feeble attempt below completely failed, would appreciate any suggestions.

Thanks

Matt

Dim irow As Long
irow = 1

Do While Cells(irow, 2) <> ""


If Format(Range("A" & irow, "dddd")) = "Monday" Then
Selection.EntireRow.Insert
Selection.EntireRow.Insert
End If

irow = irow + 1

Loop

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi Matt


I was going to write a Loop, but you will be flooded with these soon. Here is a much quicker method.

Sub Doit()
Dim rRange As Range
Set rRange = Range("B8:B" & Range("A65536").End(xlUp).Row)

rRange.FormulaR1C1 = "=IF(AND(WEEKDAY(RC[-1])=1,R[-7]C<>1),1)"
rRange = rRange.Value
rRange.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Insert
rRange.Clear

End Sub



Just ensure Column B is empty when you run it.
 
Upvote 0
Dave

The code works great how would I amend it to insert 2 rows instead of 1?

thanks

Matt
 
Upvote 0
Oh well, better late than never.

Sub Doit()
Dim rRange As Range
Set rRange = Range("B8:B" & Range("A65536").End(xlUp).Row)

rRange.FormulaR1C1 = "=IF(AND(WEEKDAY(RC[-1])=1,R[-7]C<>1),1)"
rRange = rRange.Value

With rRange.SpecialCells(xlCellTypeConstants, xlNumbers)
.EntireRow.Insert
.EntireRow.Insert
End With

rRange.Clear

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top