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]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Start Date	End Date		1/1/2019	1/2/2019	1/3/2019	1/4/2019	1/5/2019	1/6/2019	1/7/2019	1/8/2019	1/9/2019	1/10/2019	1/11/2019	1/12/2019	1/13/2019	1/14/2019	1/15/2019
2	1/1/2019	1/15/2019
Sheet1

Worksheet_Change 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

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]

Or with a simple formula

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Start Date	End Date
2	01/01/2021	15/01/2021		01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021	06/01/2021	07/01/2021	08/01/2021	09/01/2021	10/01/2021	11/01/2021	12/01/2021	13/01/2021	14/01/2021	15/01/2021
3
Master

Cell Formulas
Range	Formula
D2:R2	D2	=SEQUENCE(,B2-A2+1,A2)
Dynamic array formulas.

 

[Rick Rothstein]

Here is another Change event procedure that you can use...

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]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Start Date	End Date		1/1/2019	1/2/2019	1/3/2019	1/4/2019	1/5/2019	1/6/2019	1/7/2019	1/8/2019	1/9/2019	1/10/2019	1/11/2019	1/12/2019	1/13/2019	1/14/2019	1/15/2019
2	1/1/2019	1/15/2019
Sheet1

Worksheet_Change 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

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]

Or with a simple formula

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Start Date	End Date
2	01/01/2021	15/01/2021		01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021	06/01/2021	07/01/2021	08/01/2021	09/01/2021	10/01/2021	11/01/2021	12/01/2021	13/01/2021	14/01/2021	15/01/2021
3
Master

Cell Formulas
Range	Formula
D2:R2	D2	=SEQUENCE(,B2-A2+1,A2)
Dynamic array formulas.

This also worked perfectly, thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Rick Rothstein]

This also worked perfectly, thank you

Should I conclude that the code I offered did not work at all for you?

 

[lrobbo314]

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]

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)?