VBA to return list of dates between start and end date

[Hudson512]

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

 

[lrobbo314]

I guess it's mostly syntactic sugar and they're really just fancy loops, but Python and Javascript have ways to make one-liners like you're so adept at.

JS:

ar = Array.from({length:days},(v,k)=>new Date(sDate.setDate(sDate.getDate() + k)).toLocaleString())

Python:

ar = [sDate + datetime.timedelta(days=x) for x in range(0, (eDate-sDate).days)]

 

[lrobbo314]

Actually, the easiest one, and one I had in my head when I made the comment, is how Power Query does it.

The line of M Code to generate the list of dates is just.

Table.AddColumn(Source, "Custom", each {Number.From([StartDate])..Number.From([EndDate])})

Book1
	C	D	E	F
3	StartDate	EndDate		Custom
4	1/1/2021	1/15/2021		1/1/2021
5				1/2/2021
6				1/3/2021
7				1/4/2021
8				1/5/2021
9				1/6/2021
10				1/7/2021
11				1/8/2021
12				1/9/2021
13				1/10/2021
14				1/11/2021
15				1/12/2021
16				1/13/2021
17				1/14/2021
18				1/15/2021
Sheet1

 

[Peter_SSs]

In relation to a vba solution I would propose the code below. It addresses a number of issues that I see with one or both of the other codes

• It will not error under the following circumstances
  • If start date or end date cells are empty.
  • If a non-date value is accidentally entered in start or end date cells. (There a re a few very rare exceptions to this but I doubt they will practically occur)
• It will not error or enter a list of (presumably incorrect) dates if End date is earlier than Start date
• It won't clear the entered start and/or end date if there is not already data in columns C:?? (eg when filling in start/end dates for the first time
• It won't needlessly re-call itself when values are cleared from or entered into the sheet by the code.

I am aware that some of the issues above may have already been addressed via Data Validation, but mentioned just in case they are relevant.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rStart As Range, rEnd As Range

  Set rStart = Range("A2")
  Set rEnd = Range("B2")
  If Not Intersect(Target, Union(rStart, rEnd)) Is Nothing Then
    Application.EnableEvents = False
    Range("D2", Range("D2").End(xlToRight)).ClearContents
    If IsDate(rStart.Value) And IsDate(rEnd.Value) And rEnd.Value >= rStart.Value Then
      Range("D2").Value = rStart.Value
      Range("D2").DataSeries xlRows, xlChronological, xlDay, 1, rEnd.Value
    End If
    Application.EnableEvents = True
  End If
End Sub

Hudson512.xlsm
	A	B	C	D	E	F	G	H	I
1	Start Date	End Date
2	15/01/2021	19/01/2021		15/01/2021	16/01/2021	17/01/2021	18/01/2021	19/01/2021
Sheet1