Convert column A dates to end of month for entire column range

[Manerlao]

Hi all!

I hope everyone is keeping well.

I am trying to convert all my dates in Column A to the End of Month dates from cell A2 to the end of the column of the used range.
I would like to do this for all sheets in the workbook which have the same structure with the DateHeading in column A.

For example, my data in Column a would be:

Date Format: YYYY-MM-DD

DateHeading
2020-04-01 > to become > 2020-04-30
2020-03-22 > to become > 2020-03-31
2020-02-06 > to become > 2020-02-28
2020-01-18 > to become > 2020-01-31

My current code is as follows:

Sub ConvertTheDates1()
      Dim x As Integer
      numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      Range("A2").Select
      For x = 1 To numrows
      numrows = Excel.Application.WorksheetFunction.EOMONTH(Range("A2").Value2, 0)
         ActiveCell.Offset(1, 0).Select
      Next
   End Sub

If anyone is able to help me with this, you would be so much help!
Please let me know. I have been trying to solve this since Saturday!

Thank you all and regards,
Manerlao

 

[Manerlao]

Hi all, just an update. This VBA works, but it is really inefficient. I would need this to go down to a few thousand rows.

Range("A2") = Excel.Application.WorksheetFunction.EOMONTH(Range("A2").Value2, 0)
Range("A3") = Excel.Application.WorksheetFunction.EOMONTH(Range("A3").Value2, 0)
Range("A4") = Excel.Application.WorksheetFunction.EOMONTH(Range("A4").Value2, 0)
Range("A5") = Excel.Application.WorksheetFunction.EOMONTH(Range("A5").Value2, 0)
Range("A6") = Excel.Application.WorksheetFunction.EOMONTH(Range("A6").Value2, 0)
Range("A7") = Excel.Application.WorksheetFunction.EOMONTH(Range("A7").Value2, 0)
Range("A8") = Excel.Application.WorksheetFunction.EOMONTH(Range("A8").Value2, 0)
Range("A9") = Excel.Application.WorksheetFunction.EOMONTH(Range("A9").Value2, 0)
Range("A10") = Excel.Application.WorksheetFunction.EOMONTH(Range("A10").Value2, 0)
Range("A11") = Excel.Application.WorksheetFunction.EOMONTH(Range("A11").Value2, 0)
Range("A12") = Excel.Application.WorksheetFunction.EOMONTH(Range("A12").Value2, 0)
Range("A13") = Excel.Application.WorksheetFunction.EOMONTH(Range("A13").Value2, 0)
Range("A14") = Excel.Application.WorksheetFunction.EOMONTH(Range("A14").Value2, 0)
Range("A15") = Excel.Application.WorksheetFunction.EOMONTH(Range("A15").Value2, 0)

 

[mohadin]

Hi
Your code

Sub ConvertTheDates1()
      Dim x As Integer
      Dim numrows, i
      numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      For x = 2 To numrows + 1
      Cells(x, 2) = Format(WorksheetFunction.EoMonth(Range("A" & x), 0), "dd/mm/yyyy")
      Next
   End Sub

 

[Manerlao]

Dear Mohadin,

You are a legendary person! Thank you very much for this! It took be days to figure this out, and you solved it for me Thank you.

I just adjusted the code so that it replaces the dates in Column A to:

Sub ConvertTheDates1()
      Dim x As Integer
      Dim numrows, i
      numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      For x = 2 To numrows + 1
      Cells(x, 1) = Format(WorksheetFunction.EOMONTH(Range("A" & x), 0), "YYYY-MM-DD")
      Next
   End Sub

Thank you!
Manerlao

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe

 

[Norie]

No need to loop.

Dim rng As Range
Dim varEndDates As Variant

    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    
    varEndDates = Evaluate("DATE(YEAR(" & rng.Address & "),MONTH(" & rng.Address & ")+1,0)")

    rng.Value = varEndDates