Menu
Ask a question Report

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

Posts
3
Registration date
Thursday February 14, 2013
Last seen
February 15, 2013
 - - Latest reply: bfleming
Posts
3
Registration date
Thursday February 14, 2013
Last seen
February 15, 2013
 - Feb 15, 2013 at 07:01 AM
Excel Ask a question Report
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 

2 replies

Best answer
Report Respond to bfleming
Posts
3
Registration date
Thursday February 14, 2013
Last seen
February 15, 2013
1
Thank you
It would be a blank row. Thanks!

Thank you, bfleming 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2618 users this month

Report
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 -
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
Report
bfleming
Posts
3
Registration date
Thursday February 14, 2013
Last seen
February 15, 2013
 -
Thanks, will give it a shot! Much appreciated!
Report Respond to rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
0
Thank you
For that newly added row, would it be just a blank for or would it have the missing date written too
Ask a question