Fill in missing dates and data VBA

[emilemil222]

Hi All,

I have a data of 3 years daily exchange rate history. However, some dates (weekends, holidays) with corresponding rates are missing. I would like to fill in missing dates and copy the previous day's rates (for which data is missing) to missing date. So, in the example, the dates 15/01/2017 & 16/01/2017 are missing. So it is required to insert two rows for these dates and copy 13/01/2017's data there. Is there a possibilty to do this on VBA as I need to do this for three year's data?

Many thanks,

 

[alansidman]

Suggest you add your file sample using XL2BB so that we don't have to re-type your data. Help us to help you.

 

[Mentor82]

Hi,
Welcome to MrExcel.
Check if that's what you're looking for

Sub FillInMissingData()
    Dim lRow&
    Dim i&
    Const mainCol$ = "A"
    
    If Range(mainCol & 1).CurrentRegion.Rows.Count = 1 Then
        MsgBox "No data found"
        Exit Sub
    End If
    
    i = 2
    Do While Cells(i, mainCol) <> ""
        If Cells(i, mainCol).Offset(1, 0) - Cells(i, mainCol) > 1 Then
            Rows(i).Offset(1).Insert
            Cells(i, mainCol).EntireRow.Copy Cells(i, mainCol).Offset(1, 0)
            Cells(i, mainCol).Offset(1, 0) = Cells(i, mainCol) + 1
        End If
        i = i + 1
    Loop
    
    MsgBox "Done"
    
    
End Sub