Previous Topic Next Topic

Code for macro that conditionally inserts rows

Solved/Closed
bfleming Posts 3 Registration date Thursday February 14, 2013 Status Member Last seen February 15, 2013 - Feb 14, 2013 at 07:14 AM
bfleming Posts 3 Registration date Thursday February 14, 2013 Status Member Last seen February 15, 2013 - Feb 15, 2013 at 07:01 AM
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!





2 responses

Answer 1 / 2
bfleming Posts 3 Registration date Thursday February 14, 2013 Status Member Last seen February 15, 2013 2
Feb 14, 2013 at 08:50 AM
It would be a blank row. Thanks!
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 14, 2013 at 10:57 AM
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
bfleming Posts 3 Registration date Thursday February 14, 2013 Status Member Last seen February 15, 2013 2
Feb 15, 2013 at 07:01 AM
Thanks, will give it a shot! Much appreciated!

Similar discussions

macro for inserting multiple rows between data
vba dummy -
TrowaD -

1 response
Copy Rows from one sheet to others based on a condition
EmrsnMktgAnyst -
TrowaD -

6 responses
Excel VBA copy and paste loop with logic condition
Yogibearv -
Yogibearv -

6 responses
Merge rows if it meets criteria in Excel
cacaip89 -
rizvisa1 -

6 responses
Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response
Copy rows to other sheets based on value in column
LeedMe -
vcoolio -

6 responses