Relative range selection/comparison

[Jim Kelly]

Hi all,
I'm trying to select two cells (with date values) and compare the
values to see if the dates are concurrent. I have the compare part
down using the DateDiff function, but I'm having trouble with
selecting a1 and a2, then if they are not concurrent, inserting a row
and making the first cell (of the new row) the next date after a1,
then moving on to the new a2 and a3, comparing them, and so on through
the list. So to put it simply, I'm wanting to take a list of dates
and make sure there are no gaps, filling in when necessary.
Does this make sense? If you have seen this somewhere online, please
point me the way. I don't mind researching it out, but I'm unsure of
where to start.
Thanks in advance!
Jim

 

[JE McGimpsey]

One way:

Public Sub FillInDates()
Dim i As Long
Dim nDiff As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
With Cells(i - 1, 1)
nDiff = .Offset(1, 0).Value - .Value
If nDiff > 1 Then
.Offset(1, 0).Resize(nDiff - 1).EntireRow.Insert
.AutoFill .Resize(nDiff), xlFillDays
End If
End With
Next i
End Sub

 

[Jim Kelly]

Thank you Mr. McGimpsey, that worked like a charm!!
Jim