VBA code help - Add a line for each missing date with the start and end date defined in a cell

Question

I now have the code below, which inserts a line for each date that is absent but not for dates that are missing at the end or beginning of the month. Could someone please rewrite the code to add all missing dates between a start date and an end date? Cells A2 and B2 on the "Summary" worksheet's start and finish dates should be easily editable since they would need to be updated on a monthly basis. It's also important to note that data from the cell below is copied for each line that is added.

Dim wks As Worksheet
Set wks = Worksheets("NAV_REPORT_FSIGLOB1")

Dim lastRow As Long
lastRow = Range("D2").End(xlDown).Row

For i = lastRow To 2 Step -1
    curcell = wks.Cells(i, 4).Value
    prevcell = wks.Cells(i - 1, 4).Value

    Do Until curcell - 1 <= prevcell
        wks.Rows(i).Copy
        wks.Rows(i).Insert xlShiftDown

        curcell = wks.Cells(i + 1, 4) - 1
        wks.Cells(i, 4).Value = curcell
    Loop
Next i 

Below is an example of the data before updating

Below is how I would like the data after running macro.

Any help would be greatly appreciated.

Answer 1

Try this:

Sub FillDates()

    Dim wks As Worksheet, i As Long, n As Long
    Dim dt1 As Date, dt2 As Date, x As Long, d As Long
    
    Set wks = Worksheets("NAV_REPORT_FSIGLOB1")
    With wks
        'make start 1st
        dt1 = .Cells(2, "D")
        If Day(dt1) > 1 Then
            .Rows(2).Copy
            .Rows(2).Insert xlShiftDown
            .Cells(2, "D") = DateSerial(Year(dt1), Month(dt1), 1)
            n = n + 1
        End If

        i = .Cells(.Rows.Count, "D").End(xlUp).Row
        Do
            .Cells(i, "D").Select
            dt1 = .Cells(i - 1, "D")
            dt2 = .Cells(i, "D")
            
            d = DateDiff("d", dt1, dt2)
            If d = 1 Then
                i = i - 1
            ElseIf d > 1 Then
                .Rows(i).Copy
                .Rows(i).Insert xlShiftDown
                .Cells(i, "D") = DateAdd("d", -1, dt2)
                n = n + 1
            ElseIf d < 1 Then
                MsgBox "Date sequence error", vbCritical
                Exit Sub
            End If
            
            ' escape infinite loop
            x = x + 1
            If x > 100 Then
                 MsgBox "Too many iterations > 100", vbCritical
                 Exit Sub
            End If
        Loop While i > 2
    
    End With
    MsgBox n & " rows added"

End Sub