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 here.

**Additional Resources**

The following tutorials explain how to perform other common tasks using VBA:

How to Sort by Date in VBA

How to Sum If Between Two Dates in VBA

How to Countif Greater than Date in VBA