Get all dates between 2 dates in vba

[Shantanu_4612]

I am a newbie in vba and I am trying to get in vba all dates between 2 dates, for example I will call the function with the parameters 01-01-2015 and 15-01-2015, and I will get in return an array with all the dates possibles, i.e :

This is the Data that I have;

<tbody>
[TD="class: xl71"]ID[/TD]
[TD="class: xl72, width: 73"]Start Date[/TD]
[TD="class: xl72, width: 73"]End Date[/TD]
[TD="class: xl73, width: 103"]Code[/TD]

[TD="class: xl68, align: right"]1234567[/TD]
[TD="class: xl69, align: right"]03-10-2016[/TD]
[TD="class: xl69, align: right"]15-10-2016[/TD]
[TD="class: xl70"]ABC_987654321[/TD]

[TD="class: xl65, align: right"]3456789[/TD]
[TD="class: xl66, align: right"]10-09-2016[/TD]
[TD="class: xl66, align: right"]20-09-2016[/TD]
[TD="class: xl67"]ABC_123456789[/TD]

</tbody>

The Result should be as below, and should stop when finds blanks in start date

[TABLE="width: 232"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]03-10-2016[/TD]
[TD]ABC_987654321[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]04-10-2016[/TD]
[TD]ABC_987654321[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]05-10-2016[/TD]
[TD]ABC_987654321[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]10-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]11-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]12-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]13-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]14-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]15-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]16-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]17-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]18-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]19-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]20-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
</tbody>[/TABLE]

Please help me with this its a bit urgent, my job is on stake

 

[Shantanu_4612]

Try this on a small sample to make sure you getting the right results on sheet "Raw".
NB:- If the results in "Raw" get over one million rows the results Move 3 columns across and starting again with row1.
I have tried this on 200K rows giving a result of approx. 2.4 million rows.
The code took about 3Minutes to run.

[COLOR="Navy"]Sub[/COLOR] MG05Oct57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] t
ac = 1
t = Timer
Application.ScreenUpdating = False
[COLOR="Navy"]With[/COLOR] Sheets("UX_Dump")
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("BM2"), .Range("BM" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
c = 1
[COLOR="Navy"]With[/COLOR] Sheets("Raw")
.Range("A1:C1").Value = Array("ID", "Date", "Code")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Dt = Dn.Value To Dn.Offset(, 2).Value
        c = c + 1
        [COLOR="Navy"]If[/COLOR] c >= 1000000 [COLOR="Navy"]Then[/COLOR] ac = ac + 3: c = 1
        .Cells(c, ac) = Dn.Offset(, -22).Value
        .Cells(c, ac + 1) = Dt
        .Cells(c, ac + 2) = Dn.Offset(, -2).Value
    [COLOR="Navy"]Next[/COLOR] Dt
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
Application.ScreenUpdating = True
MsgBox Timer - t
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Thanks a lot!!
This worked perfectly, with just a run time of 5 mins, even on our low configured computers and with a data of 5k entries.

Just one more question,
Can we convert a formula in a macro, n if yes I would love to know how,
As it will help me reduce the run time of all my templates

 

[Shantanu_4612]

Can you give me a script, a default script,
In which I can enter the cells from where the data has to be picked, where the output should reflect and the formula.
With a default setting of, when cell goes blank, macro should stop