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!

Macro to insert row in excel based on criteria
Insert gif in excel - Guide
Insert draft watermark in word on all pages - Guide
Excel macro to create new sheet based on value in cells - Guide
How to insert photo in word for resume - Guide
Insert check mark in word - Guide

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

Copy Rows from one sheet to others based on a condition

Excel VBA copy and paste loop with logic condition

Merge rows if it meets criteria in Excel

Code to move rows into another sheet based on certain values

Copy rows to other sheets based on value in column

Similar discussions

Subscribe To Our Newsletter!