inserting rows conditionally

Posted by doug on October 08, 2000 11:19 AM

I have data (date in col1 yes or no in col2) exported from another application; I need to insert rows between each record equal to the number of days between the date in one record and the date in the next record; the dates range back to 1996 and could have any number of days between each record; then I need to fill the inserted rows by duplicating the row just before the inserted rows

Posted by Robin Glynn on October 08, 2000 4:09 PM

This code does what you asked for (assuming the date format of your imported data increments by 1 per day as Excel does)......

Dim Value1 As Long, Value2 As Long

Sub InsertRowsConditionally()
[a1].Select
Do
Value1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then Exit Do
Value2 = ActiveCell.Value
If Value2 - Value1 > 1 Then
Range(ActiveCell, ActiveCell.Offset(Value2 - Value1 - 2)).EntireRow.Insert
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(Value2 - Value1 - 2, 1)).FillDown
ActiveCell.Offset(Value2 - Value1 - 1).Select
End If
Loop
End Sub

.....But if you meant to have the dates increase by 1 day on each line then it will need to be modified to include a 'DataSeries Step:=1' the increment the date.