Insert Rows Based On Date In Cells Vba

First, let’s start with a tip about posting data and code in this forum. This tip can be used for both the data table in your first post and formatted code (indents, etc).
To line up data in your post, please use the pre tags found above the Reply box.

1 – Click the pre icon found above the Reply box.
2 – Enter your data between the tags.
3 – Click Preview Follow Up to see if you like the way it looks.
4 – If you need to fix the layout, fix it in the Message box below the Preview box.
5 – Click the “Check To Show Confirmation Page Again” box.
6 – Click either Confirm button to Preview the post again.

Repeat steps 4 – 6 as often as necessary until you like the way the post looks and then click Confirm.

As an example, the data table in your OP could be made to look like this when used with the pre tags:

A B C D
9 8/24/2010 p 15243
10 8/24/2010 p 1500
11 8/29/2010 n 45
12 8/29/2010 p 7410
13 8/29/2010 p 621
14 8/29/2010 n 275
15 9/2/2010 p 15243
16 9/2/2010 p 1500
Now let’s mention a very basic coding practice that you should be aware of:

Rarely do you have to Select a object with VBA to perform an action on it. You can refer to the object directly. For example,

ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
can be written as:

ActiveCell.EntireRow.Insert Shift:=xlDown
In fact, you don’t even have refer to the active cell, you can refer to any cell directly:

Range(“B9”).EntireRow.Insert Shift:=xlDown
Or, if using a variable (as we will below):

myRow = 9
Range(“B” & myRow).EntireRow.Insert Shift:=xlDown
Selecting objects makes for bulky and inefficient code.

That said, when I review your code, I don’t see any code where you are checking for a change in the date. If the change in date is the trigger to insert a row, then obviously you need to check for that condition.

The easiest way to do that is to loop through the range and compare each cell to the one below it. If they are not equal to each other, then insert a row between the change.

Now since we are going to use a For-Next loop, we need to know where to start, and where to stop. Based on your example code, I’m assuming that the dates start in B9, so we can start the loop there. To determine the end of the loop, we’ll let VBA determine that for us.

Finally, since we are going to insert rows, which would mess up the For-Next loop, we need to start from the bottom. If we start from the top, insert a row and then let the For-Next counter increment, it will be looking at the new row, which won’t match the row below it and the code would just keep inserting rows at the same spot.

However, if we start at the bottom and count backwards, then the counter will look at a row, insert one below it if required and then move up, thereby always checking cells with a date in them.

So here we go:

Option Explicit
Sub InsertAtDateChange()
Dim lastRow, chkRw As Integer
‘Determine last row with data in Column B
lastRow = Range(“B” & Rows.Count).End(xlUp).Row
‘Loop from bottom of list to Row 9, in reverse order
For chkRw = lastRow To 9 Step -1
‘Compare the current cell to the one below it
‘If they don’t match, insert a row row below the current Row
If Range(“B” & chkRw) Range(“B” & chkRw + 1) Then
Range(“B” & chkRw + 1).EntireRow.Insert shift:=xlDown
End If
‘Decrement the counter and do it again
Next
End Sub