You can use the EoMonth method in VBA to return the last day of the month for a given date.

Here is one common way to use this method in practice:

Sub LastDayOfMonth()

Dim i As Integer

For i = 2 To 11
    Range("C" & i).Value = Application.WorksheetFunction.EoMonth(Range("A" & i), 0)
    Range("C" & i).NumberFormat = "m/d/yyyy"
Next i

End Sub

This particular macro finds the last day of the month for each date in the range A2:A11 and displays this date in the corresponding cell in the range C2:C11.

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

Example: How to Use EoMonth in VBA

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

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

We can create the following macro to do so:

Sub LastDayOfMonth()

Dim i As Integer

For i = 2 To 11
    Range("C" & i).Value = Application.WorksheetFunction.EoMonth(Range("A" & i), 0)
    Range("C" & i).NumberFormat = "m/d/yyyy"
Next i

End Sub

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

Notice that column C contains the last day of the month for each corresponding date in column A.

Note that we used the NumberFormat property to display the date values in a recognizable date format in column C instead of serial numbers, which is the default format in Excel.

Also note that the last argument of the EoMonth method specifies how many months ahead or behind to use when calculating the last day of the month.

In this example we used a value of 0 to specify that we’d like to find the last day of the current month.

Note: You can find the complete documentation for the EoMonth method in VBA .