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;

ID	Start Date	End Date	Code
1234567	03-10-2016	15-10-2016	ABC_987654321
3456789	10-09-2016	20-09-2016	ABC_123456789

<tbody>
</tbody>

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

ID	Date	Code
1234567	03-10-2016	ABC_987654321
1234567	04-10-2016	ABC_987654321
1234567	05-10-2016	ABC_987654321
3456789	10-09-2016	ABC_123456789
3456789	11-09-2016	ABC_123456789
3456789	12-09-2016	ABC_123456789
3456789	13-09-2016	ABC_123456789
3456789	14-09-2016	ABC_123456789
3456789	15-09-2016	ABC_123456789
3456789	16-09-2016	ABC_123456789
3456789	17-09-2016	ABC_123456789
3456789	18-09-2016	ABC_123456789
3456789	19-09-2016	ABC_123456789
3456789	20-09-2016	ABC_123456789

<tbody>
</tbody>

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