VBA code for deleting rows if cell references date of prior month from current month.

[jgome21]

Hi Guys,

I am having trouble writing a VBA code that would delete all rows if a cell contains a date of the prior month of the current month (i.e. for Jan. I would like to delete all rows that reference a date of 12/31/17 or prior). So far I have this, but I am currently stuck. Any tips would be appreciated. Thanks.

Sheets("3. Docs Charged").Select
With Sheets("3. Docs Charged")
lastrow = Cells(Rows.Count, 14).End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 14).Value2 < Date Then Rows(i).EntireRow.Delete
Next i
End With

 

[Joe4]

Do you want it limited to just the one month prior to the current month (i.e. only December 2017 right now), or do you want it to delete ANY month prior to the current one?

 

[jgome21]

To be able to delete rows for any month prior to the current one.

Thank you.

 

[Joe4]

Try this:

Sub MyDeleteRows()

    Dim lastrow As Long
    Dim y As Long
    
    Sheets("3. Docs Charged").Select
    With Sheets("3. Docs Charged")
        lastrow = Cells(Rows.Count, 14).End(xlUp).Row
        For i = lastrow To 2 Step -1
            If (Cells(i, 14).Value2 < Date) And _
                ((Month(Cells(i, 14)) < Month(Date)) Or _
                (Year(Cells(i, 14)) < Year(Date))) _
                Then Rows(i).EntireRow.Delete
        Next i
    End With
    
End Sub

 

[jgome21]

Worked perfectly. Thank you Joe. I appreciate it.

 

[Joe4]

You are welcome.