Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
3
Registration date
Thursday February 14, 2013
Status
Member
Last seen
February 15, 2013
 -
Posts
3
Registration date
Thursday February 14, 2013
Status
Member
Last seen
February 15, 2013
 -
Hello, I know this has been asked and answered often, in particular here: https://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
Related:

2 replies

Answer 1 / 2
Posts
3
Registration date
Thursday February 14, 2013
Status
Member
Last seen
February 15, 2013
 2
It would be a blank row. Thanks!
2
Thank you
Thank you

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

CCM 2942 users have said thank you to us this month

Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 764
This is a slight twist on code provided by TrowaD at https://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
Posts
3
Registration date
Thursday February 14, 2013
Status
Member
Last seen
February 15, 2013
 2
Thanks, will give it a shot! Much appreciated!
Answer 2 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 764
For that newly added row, would it be just a blank for or would it have the missing date written too
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!