inserting blank row at end of work week

[Enzo_Matrix]

my table sorts based on date and I want to insert a blank line at the end of the work week so that I can get a clear idea of what needs to be done.

Is there a VBA code I and use that will accomplish this?

 

[jproffer]

I think this is what you're talking about. Please test on a COPY of your worksheet/workbook before running it on the real data to make sure it is, in fact, what you want.

Sub test()
Dim Col As String
Dim LR As Long
Dim i As Long

Col = "A"
LR = Cells(Rows.Count, Col).End(xlUp).Row

For i = LR To 1 Step -1
    On Error Resume Next
    If Cells(i, Col).Value - Cells(i - 1, Col).Value > 1 Then
    On Error GoTo 0
        Cells(i, Col).EntireRow.Insert
    End If
Next i



End Sub

I started with this:

Excel 2013/2016

	A

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]6/4/2018[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/5/2018[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6/6/2018[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6/7/2018[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6/8/2018[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/11/2018[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6/12/2018[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6/13/2018[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6/14/2018[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6/15/2018[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6/18/2018[/TD]

</tbody>

Sheet1​

and ended with this:

Excel 2013/2016

	A

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6/4/2018[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6/5/2018[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6/6/2018[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/7/2018[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6/8/2018[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6/11/2018[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6/12/2018[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6/13/2018[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6/14/2018[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6/15/2018[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6/18/2018[/TD]

</tbody>

Sheet1​

This is assuming saturdays and sundays (or at least one or the other) are missing from your list of date. If you have continuous dates all the way down, that particular code wont work.

 

[Enzo_Matrix]

That's great Thanks!

my sheet has a few things that are from the previous few weeks and the sheet grew dramatically with all the extra lines inserted. Is there a way to only insert a blank line for the current week or only weeks after todays date?

 

[jproffer]

Sure. Just a couple extra lines. Try this one out: (on a copy, again )

Sub test()
Dim Col As String
Dim LR As Long
Dim i As Long

Col = "A"
LR = Cells(Rows.Count, Col).End(xlUp).Row

For i = LR To 1 Step -1
    If Cells(i, Col).Value >= Date Then
        On Error Resume Next
        If Cells(i, Col).Value - Cells(i - 1, Col).Value > 1 Then
        On Error GoTo 0
            Cells(i, Col).EntireRow.Insert
        End If
    End If
Next i

End Sub

 

[Enzo_Matrix]

that is beyond amazing. There is a small bug that pops up for some reason.

My headers are in row 3, dates start in row 4 and for some reason it is adding a blank row into 3 and shifting everything down

 

[jproffer]

Change:

LR To 1

to:

LR To 4

If the first 5 rows below your header are always a full week, you could even make it higher than 4 if you needed to. Anything up to, say....8...9.

Just play with that line and see how it works best for you.

You'll need to make sure the step stays at -1