Fill in missing dates

Question

I am working on big data set and manually filling the missing dates is very tedious work. I want to automate this by applying Excel formula or VBA code which can help me fill all these missing dates.

Please have a look at the data below.

The first table is the input I get, and I want the 2nd table as my output. Yellow cells show the missing dates which should be same as its previous row.

Answer 1

Just need to loop through each cell in the date column. If blank assign the date value; if not blank then update the LastDate variable.

Sub FillInDates()
    Dim rg As Range
    Dim aCell As Range
    Dim LastDate As Date

    'set this to your date column
    Set rg = ActiveSheet.UsedRange.Columns(1).Offset(1, 0)

    For Each aCell In rg.Cells
        If IsEmpty(aCell) Then
            aCell.Value = LastDate
        Else
            LastDate = aCell.Value
        End If
    Next
End Sub

Answer 2

I'd use this function:

Sub Fill_In_Missing_Dates()
    Dim rng As Range
    Dim cell As Range
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("B2:B11")
    For Each cell In rng
        If cell.Value = vbNullString Then cell.Value = cell.Offset(-1).Value
    Next
End Sub

Just change Sheet1 and B2:B11 with your actual sheet name and cells.

Answer 3

An alternative if you don't want to use VBA:

Select the column, go to Home - Find & Select - Go to Special - select Blanks and hit OK. You have all the blank cells selected. Now enter a formula referencing the cell above the active cell (= sign and up arrow) and hit ctrl+enter to paste it into all the selected cells.

You might want to copy-paste values the whole column afterwards.

On huge ranges, (e.g. more than 100000 cells) this method becomes slow, so I too recommend the VBA solutions on big tables.

---

A quick benchmark: both ZygD's and D_Bester's solutions ran just under 2 seconds on 130000 rows. If I run them on the same set of cells, there is no measurable speed difference between them. On the other hand, using the specialcells(xlcelltypeblanks) takes 25 seconds just to find the empty cells.