adding missing dates

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
I have a table with several columns. The first is a name and the second is a date. For each name there are several dates. The dates should be sequential. If there is a gap of one or more days, I would like to add a line with the name and the missing date.
david3/5/19
david3/6/19
david3/8/19
david3/9/19
John3/11/19
John3/15/19
John3/16/19

<tbody>
</tbody>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
You could do it with Power Query, but here is a VBA solution

Code:
Sub AddDates()
Dim firstdate As Date, lastdate As Date
Dim numrows As Long, numdates As Long, numinsert As Long
Dim lastrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 2 Step -1
        
            lastdate = .Cells(i, "B").Value
            numrows = Application.CountIf(.Columns(1), .Cells(i, "A").Value)
            firstdate = .Cells(i - numrows + 1, "B").Value
            numdates = lastdate - firstdate + 1
            numinsert = numdates - numrows
            If numinsert > 0 Then .Rows(i + 1).Resize(numinsert).Insert
            .Cells(i - numrows + 1, "A").Resize(numdates).Value = .Cells(i, "A").Value
            .Cells(i - numrows + 1, "B").Resize(numdates).DataSeries Rowcol:=xlColumns, _
                                                                     Type:=xlChronological, _
                                                                     Date:=xlDay, _
                                                                     Step:=1, Stop:=lastdate, _
                                                                     Trend:=False
            i = i - numrows + 1
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this
- code inserts a new sheet which is a copy of the original sheet and the macro works on that data
- which makes it easier to rerun whilst testing
- source data remains undamaged!
- amend sheet name in the code to match the name of the sheet containing your data


Source data Starts in A2
Excel 2016 (Windows) 32 bit
A
B
1
NameDate
2
david
03/05/2019​
3
david
03/06/2019​
4
david
03/08/2019​
5
david
03/09/2019​
6
John
03/11/2019​
7
John
03/15/2019​
8
John
03/16/2019​
Sheet: Sheet1

New sheet created which looks like this
Excel 2016 (Windows) 32 bit
A
B
1
NameDate
2
david
03/05/2019​
3
david
03/06/2019​
4
david
03/07/2019​
5
david
03/08/2019​
6
david
03/09/2019​
7
John
03/11/2019​
8
John
03/12/2019​
9
John
03/13/2019​
10
John
03/14/2019​
11
John
03/15/2019​
12
John
03/16/2019​
Sheet: Sheet1 (2)

Code:
Sub InsertDates()
    Dim ws As Worksheet, r As Long, lastRow As Long, diff As Integer
    Dim A1 As Range, A2 As Range, B1 As Range, B2 As Range
    
    Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Copy After:=Worksheets(Sheets.Count)
    Set ws = Worksheets(Sheets.Count)
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = lastRow - 1 To 2 Step -1
        Set A1 = ws.Cells(r, "A")
        Set A2 = A1.Offset(1)
        Set B1 = A1.Offset(, 1)
        Set B2 = B1.Offset(1)
        diff = B2 - B1
        
        If A1 = A2 And diff > 1 Then
            A2.Resize(diff - 1).EntireRow.Insert
            A1.Resize(diff) = A1
            B1.AutoFill Destination:=B1.Resize(diff), Type:=xlFillDefault
        End If
    Next r
End Sub


I have a table
- is this a structured table created with Insert Table (the code also works)

Excel 2016 (Windows) 32 bit
A
B
1
NameDate
2
david
03/05/2019​
3
david
03/06/2019​
4
david
03/08/2019​
5
david
03/09/2019​
6
John
03/11/2019​
7
John
03/15/2019​
8
John
03/16/2019​
Sheet: Sheet1

Excel 2016 (Windows) 32 bit
A
B
1
NameDate
2
david
03/05/2019​
3
david
03/06/2019​
4
david
03/07/2019​
5
david
03/08/2019​
6
david
03/09/2019​
7
John
03/11/2019​
8
John
03/12/2019​
9
John
03/13/2019​
10
John
03/14/2019​
11
John
03/15/2019​
12
John
03/16/2019​
Sheet: Sheet1 (4)
 
Upvote 0
Thanks for the quick response. It is working. Thanks again

<tbody>
</tbody>
You could do it with Power Query, but here is a VBA solution

Code:
Sub AddDates()
Dim firstdate As Date, lastdate As Date
Dim numrows As Long, numdates As Long, numinsert As Long
Dim lastrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 2 Step -1
        
            lastdate = .Cells(i, "B").Value
            numrows = Application.CountIf(.Columns(1), .Cells(i, "A").Value)
            firstdate = .Cells(i - numrows + 1, "B").Value
            numdates = lastdate - firstdate + 1
            numinsert = numdates - numrows
            If numinsert > 0 Then .Rows(i + 1).Resize(numinsert).Insert
            .Cells(i - numrows + 1, "A").Resize(numdates).Value = .Cells(i, "A").Value
            .Cells(i - numrows + 1, "B").Resize(numdates).DataSeries Rowcol:=xlColumns, _
                                                                     Type:=xlChronological, _
                                                                     Date:=xlDay, _
                                                                     Step:=1, Stop:=lastdate, _
                                                                     Trend:=False
            i = i - numrows + 1
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks for the quick response. I tested it and it is working. Thanks again, you saved me a lot of time!!!!
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top