VBA to return list of dates between start and end date

Hudson512

New Member
Joined
Mar 21, 2011
Messages
8
Office Version
  1. 365
Hi,

First off, let me start by saying I have very little experience in VBA so apologies in advance!

I am hoping someone could help me with a code that pulls all the dates listed between two cells (start and end date).

I have tried a couple which seem to work but I can't work out how to do a couple of things:

1. Have the dates listed horizontally rather than vertically
2. Replace the listed dates automatically when the start / end date is updated

Any help would be greatly appreciated.

Thanks
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,025
Office Version
  1. 365
Platform
  1. Windows
How about this?

Book1
ABCDEFGHIJKLMNOPQR
1Start DateEnd Date1/1/20191/2/20191/3/20191/4/20191/5/20191/6/20191/7/20191/8/20191/9/20191/10/20191/11/20191/12/20191/13/20191/14/20191/15/2019
21/1/20191/15/2019
Sheet1


Worksheet_Change Code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2:B2"), Target) Is Nothing Then
    Range("D1", Range("D1").End(xlToRight)).ClearContents
    listDates
End If
End Sub

Standard Module
VBA Code:
Sub listDates()
Dim DT() As Variant:    DT = Range("A2:B2").Value2
Dim RES As Variant:     ReDim RES(1 To (DT(1, 2) - DT(1, 1)) + 1)

For i = 1 To UBound(RES)
    RES(i) = DT(1, 1) + i - 1
Next i

Range("D1").Resize(1, UBound(RES)).Value2 = RES
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
Or with a simple formula
+Fluff v2.xlsm
ABCDEFGHIJKLMNOPQRS
1Start DateEnd Date
201/01/202115/01/202101/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/202111/01/202112/01/202113/01/202114/01/202115/01/2021
3
Master
Cell Formulas
RangeFormula
D2:R2D2=SEQUENCE(,B2-A2+1,A2)
Dynamic array formulas.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,685
Office Version
  1. 2010
Platform
  1. Windows
Here is another Change event procedure that you can use...
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Arr As Variant
  If Target.Address(0, 0) Like "[AB]2" Then
    Arr = Evaluate("TRANSPOSE(ROW(" & [A2].Value2 & ":" & [B2].Value2 & "))")
    Range("D2", Cells(2, Columns.Count).End(xlToLeft)).Clear
    With [D2].Resize(, UBound(Arr))
      .Value = Arr
      .NumberFormat = "m/d/yyyy"
    End With
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Hudson512

New Member
Joined
Mar 21, 2011
Messages
8
Office Version
  1. 365

ADVERTISEMENT

How about this?

Book1
ABCDEFGHIJKLMNOPQR
1Start DateEnd Date1/1/20191/2/20191/3/20191/4/20191/5/20191/6/20191/7/20191/8/20191/9/20191/10/20191/11/20191/12/20191/13/20191/14/20191/15/2019
21/1/20191/15/2019
Sheet1


Worksheet_Change Code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2:B2"), Target) Is Nothing Then
    Range("D1", Range("D1").End(xlToRight)).ClearContents
    listDates
End If
End Sub

Standard Module
VBA Code:
Sub listDates()
Dim DT() As Variant:    DT = Range("A2:B2").Value2
Dim RES As Variant:     ReDim RES(1 To (DT(1, 2) - DT(1, 1)) + 1)

For i = 1 To UBound(RES)
    RES(i) = DT(1, 1) + i - 1
Next i

Range("D1").Resize(1, UBound(RES)).Value2 = RES
End Sub
Worked perfectly, thank you so much :)
 

Hudson512

New Member
Joined
Mar 21, 2011
Messages
8
Office Version
  1. 365
Or with a simple formula
+Fluff v2.xlsm
ABCDEFGHIJKLMNOPQRS
1Start DateEnd Date
201/01/202115/01/202101/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/202111/01/202112/01/202113/01/202114/01/202115/01/2021
3
Master
Cell Formulas
RangeFormula
D2:R2D2=SEQUENCE(,B2-A2+1,A2)
Dynamic array formulas.
This also worked perfectly, thank you :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,025
Office Version
  1. 365
Platform
  1. Windows
I liked your solution Rick. I wondered if there was a Javascripty array way to do it. I'll have to put that one in the toolbelt!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,685
Office Version
  1. 2010
Platform
  1. Windows
I liked your solution Rick. I wondered if there was a Javascripty array way to do it.
Thanks! I haven't looked into Javascript yet so I can't help with that. I'm guessing, though, that it will need an equivalent for Application's Evaluate function. Does Javascript expose an equivalent to the Application object (specifically Application.WorksheetFunction)?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,583
Messages
5,597,022
Members
414,116
Latest member
sfullnet

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
Top