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

 

[AndreDo]

hahahaha this forum is awesome! one last question. how do i sort the results so that the newest date starts first? i tried every combo of applying the sort command but keeps getting a debug error.

For example it's currently populating like this:

9/30/2018
10/31/2018
11/30/2018

How do i sort to be like this:
11/30/2018
10/31/2018
9/30/2018

thank you in advance.

lastly, is there a good book to read up on VBA?

 

[Tom.Jones]

Sort Z-A

 

[Rick Rothstein]

Here is my code with the sort function included within it...

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
    [B][COLOR="#0000FF"].Sort .Cells(1), xlDescending[/COLOR][/B]
  End With
End Sub

 

[DanteAmor]

I made the adjustments to the code:

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", WorksheetFunction.EoMonth(StartD, -1) + 1, WorksheetFunction.EoMonth(EndD, -1) + 1)
  For i = 1 To n + 1
    Cells(i, 1) = WorksheetFunction.EoMonth(StartD, i - 1)
  Next
  Range("A1", Range("A" & Rows.Count).End(xlUp)).Sort Range("A1"), xlDescending
End Sub

 

[AndreDo]

thank you again for all the help @DanteAmore, @Rick Rothstein !

 

[DanteAmor]

Youre welcome & thanks for the feedback.