insert rows based on date in cells vba

October 15, 2010 at 17:09:34
Specs: Windows XP

i have data like this:

8/24/2010 p 15243
8/24/2010 p 1500
8/29/2010 n 45
8/29/2010 p 7410
8/29/2010 p 621
8/29/2010 n 275
9/2/2010 p 15243
9/2/2010 p 1500

what I need excel to do is insert a row (blank) after every specific date (for e.g. between the last entry of 8/24 and the first of 8/29).

any kind of code that I have written just does not seem to work..

please HELP!!!


See More: insert rows based on date in cells vba

October 15, 2010 at 20:31:22
Rather than just giving you the code to do what you want, I think it would be beneficial to you if you posted what you have tried and we'll help you fix it.

I'm not asking you to do that to embarrass you or point out shortcomings in your VBA skills, but rather to help you learn from your trials and errors.

Sometimes explaining why something doesn't work is more educational that just telling you want does.

Report •

October 15, 2010 at 20:43:32
@derbydad03: I understand that, absolutely.

Here's the code. (and I'm not embarrassed) :-)
Sub testloop()
Dim vdate As Date

If ActiveCell.Value < CLng(vdate) Then
'do nothing
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

i know this is a bit wrong (a whole lot), but, I'm still trying to learn.. :-)

Thanks for responding...

Report •

October 16, 2010 at 12:45:54
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,

  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
End Sub

Report •

Related Solutions

October 16, 2010 at 13:27:36

thanks for the posting tip; I'm new here (and this is my first post), hence the errors..

the code worked like a charm...

thnx a ton...

Report •

Ask Question