# Insert Missing Date VBA Question

#### Netsurfr

##### New Member
This VBA code inserts missing dates in column A which works when the month does not start with the 1st. When the month does start with the first I get the following duplication. What am I missing to prevent this? Thank you in advance for your help.

Sub Insert_Days()
Dim r As Long, d As Date
r = 2 'start row
EOM = DateSerial(Year(Range("A" & r)), Month(Range("A" & r)) + 1, 0) 'End of month
d = DateSerial(Year(Range("A" & r)), Month(Range("A" & r)), 1) 'Beginning of month
Application.ScreenUpdating = False
Do While d <= EOM
DoEvents
If Range("A" & r) > d Then
Rows(r).Insert
Range("A" & r).Value = d
d = d + 1
ElseIf Range("A" & r) = "" Then
Range("A" & r).Value = d
d = d + 1
End If
r = r + 1
If Range("A" & r) = d Then d = d + 1
Loop
Application.ScreenUpdating = True
End Sub

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Joe4

Try flipping the order of these two lines:
VBA Code:
``````r = r + 1
If Range("A" & r) = d Then d = d + 1``````
so its:
VBA Code:
``````If Range("A" & r) = d Then d = d + 1
r = r + 1``````

#### Netsurfr

##### New Member
Try flipping the order of these two lines:
VBA Code:
``````r = r + 1
If Range("A" & r) = d Then d = d + 1``````
so its:
VBA Code:
``````If Range("A" & r) = d Then d = d + 1
r = r + 1``````

Yes, it works perfectly now! Thank you very much it was causing me so much distress.

You are welcome.

Replies
1
Views
82
Replies
14
Views
143
Replies
0
Views
98
Replies
5
Views
64
Replies
4
Views
98

1,127,998
Messages
5,628,046
Members
416,289
Latest member
Jbelisari

### 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.

### Which adblocker are you using?

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

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