Code for macro that conditionally inserts rows [Solved/Closed]

bfleming 3 Posts Thursday February 14, 2013Registration date February 15, 2013 Last seen - Feb 14, 2013 at 07:14 AM - Latest reply: bfleming 3 Posts Thursday February 14, 2013Registration date February 15, 2013 Last seen
- Feb 15, 2013 at 07:01 AM
Hello, I know this has been asked and answered often, in particular here: http://ccm.net/forum/affich-222200-insert-rows-on-change-of-value-in-excel-table

But I don't know the first thing about code and as helpful as the answer to that post is, I have no idea how to change the code to meet my particular requirements. That is:

I have daily data where the dates sometimes skip days, e.g.:

A1: 1/1/13
A2: 1/3/13. I need to insert a row
A3: 1/4/13

I need to insert a row for 1/2/13 between A1 and A2.

In some cases the data skips two days, e.g.:

A4: 1/5/13
A5: 1/8/13
A6: 1/9/13

In this case I'd need to insert two rows between A4 and A5 for 1/6/13 and 1/7/13, respectively.

Would someone be able to write the code for me?

Thanks in advance for the help!





See more 

4 replies

Best answer
bfleming 3 Posts Thursday February 14, 2013Registration date February 15, 2013 Last seen - Feb 14, 2013 at 08:50 AM
1
Thank you
It would be a blank row. Thanks!

Thank you, bfleming 1

Something to say? Add comment

CCM has helped 1667 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 14, 2013 at 10:57 AM
This is a slight twist on code provided by TrowaD at http://ccm.net/forum/affich-222200-insert-rows-on-change-of-value-in-excel-table#1

Sub InsertRows()
  Dim r As Long
  Dim mcol As Date
  Dim I As Long

' find last used cell in Column A
  r = Cells(Rows.Count, "A").End(xlUp).Row

 ' get value of  last used cell in column A
  mcol = Cells(r, 1).Value

 ' insert rows by looping from bottom
  For I = r To 2 Step -1
    
     'if current value is not same as the current row being tested
     If Cells(i, 1) <> mcol Then
        'both values are not same, so insert row
        Rows(i + 1).Insert
     End If
     'change date by one day back
     mcol = DateAdd("d", -1, mcol)
  Next i

End Sub
bfleming 3 Posts Thursday February 14, 2013Registration date February 15, 2013 Last seen - Feb 15, 2013 at 07:01 AM
Thanks, will give it a shot! Much appreciated!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 14, 2013 at 07:31 AM
0
Thank you
For that newly added row, would it be just a blank for or would it have the missing date written too