You can use the DateSerial() function in VBA with a value of 0 for the day argument to find the last day of the month for a date in a particular cell.

For example, you can use the following syntax to find the last day of the month for the date in cell A1 and return this date in cell B1:

dateVal = DateValue(Range("A1"))

Range("B1").Value = DateSerial(Year(dateVal), Month(dateVal)+1, 0)

For example, if cell A1 contains 1/5/2023 then cell B1 will return 1/31/2023.

The following example shows how to use this function in practice.

Example: Use VBA to Find Last Day of Month

Suppose we have the following dataset in Excel that contains information about sales made on various dates at some company:

Suppose we would like to find the last day of the month for each date in column A and return the date in the corresponding cell in column C.

We can use the following syntax to do so:

Sub FirstDayOfMonth()

Dim i As Integer

For i = 2 To 11
    dateVal = DateValue(Range("A" & i))
    Range("C" & i).Value = DateSerial(Year(dateVal), Month(dateVal)+1, 0)
Next i

End Sub

When we run this macro, we receive the following output:

Each of the dates in column C represent the last day of the month for the corresponding date in column A.

Note #1: In the code we used For i = 2 to 11 since cells A2 to A11 contained the dates we were interested in. Feel free to change this range depending on the cell range you’d like to use.

Note #2: You can find the complete documentation for the DateSerial() function in VBA .