Macro to autofill month end dates between start date and end date

[AndreDo]

Hi Guys,

I found a macro in this forum that list all the dates between end date and start date but I can't seem to figure out how to make it only display only month end dates! Can any excel ninja here help me with this?

Sub FillDates()
Dim StartD As Date, EndD As Date

StartD = Worksheets("Sheet1").Range("B1").Value
EndD = Worksheets("Sheet1").Range("B2").Value


For Row = 1 To EndD - StartD
Cells(Row, 1) = StartD + Row - 1


Next Row

End Sub

Thank you in advance,
Dre

 

[DanteAmor]

Try this

Sub FillDates()
  Dim StartD As Date, EndD As Date, i As Long, n As Long
  StartD = Worksheets("Sheet1").Range("B1").Value
  EndD = Worksheets("Sheet1").Range("B2").Value
  n = DateDiff("m", StartD, EndD)
  For i = 1 To n
    Cells(i, 1) = WorksheetFunction.EoMonth(StartD, i - 1)
  Next
End Sub

 

[AndreDo]

It works, WHOA!!!!!!! I would have never thought of that. Thank you so much @DanteAmor. One small issue is this does not display the last month's date. If i enter startdate=9/2018 and enddate=12/2018 the output is: 9/30/2018,10/31/2018, and 11/30/2018

 

[DanteAmor]

It works, WHOA!!!!!!! I would have never thought of that. Thank you so much @DanteAmor. One small issue is this does not display the last month's date. If i enter startdate=9/2018 and enddate=12/2018 the output is: 9/30/2018,10/31/2018, and 11/30/2018

Try this

Change this line:

For i = 1 To n

For this

For i = 1 To n[COLOR=#0000ff] + 1[/COLOR]

 

[Rick Rothstein]

I don't know if you will find this interesting, but here is a non-looping macro that can also be considered...

Sub FillDates()
  Dim Arr As Variant
  Arr = Evaluate("IF({1},EOMONTH(B1,ROW(1:" & 1 + DateDiff("m", [B1], [B2]) & ")-1))")
  With Range("A1").Resize(UBound(Arr))
    .NumberFormat = "m/d/yyyy"
    .Value = Arr
  End With
End Sub

 

[AndreDo]

Very interesting. I'll have to explore this a bit further...thank you for sharing @rickrothstein!

 

[AndreDo]

It's true, "not all hero wears a capes"! Thank you again @DanteAmor!

 

[DanteAmor]

It's true, "not all hero wears a capes"! Thank you again @DanteAmor!

I'm glad to help you. I appreciate your kind comments, But I am not a hero, but you met a true hero: @Rick Rothstein

 

[Rick Rothstein]

I'm glad to help you. I appreciate your kind comments, But I am not a hero, but you met a true hero: @Rick Rothstein

Wow! What a kind thing to say. However, I am not a "hero" either, just someone who is simply trying to help out when and where I am able to.

 

[DanteAmor]

Wow! What a kind thing to say. However, I am not a "hero" either, just someone who is simply trying to help out when and where I am able to.

Those are the classic words of a hero (a modest hero).