inserting blank row at end of work week

Enzo_Matrix

Board Regular
Joined
Jan 9, 2018
Messages
113
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.

Code:
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
Sure. Just a couple extra lines. Try this one out: (on a copy, again :) )

Code:
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
 
Upvote 0
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
 
Upvote 0
Change:
Code:
LR To 1

to:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

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