Delete Rows based on Values in Two Columns-CodePudding

I have an excel sheet that I need to delete rows based on the values from two different cells.

The initial excel sheet is created from a report that will populate the data from the previous month up until the current day. Depending on the day the report is ran I could have an extra three days or four days worth of data that I do not need. I would like to delete the rows that I do not need based on the month and time columns.

Simplified Excel Sheet

I would like to delete everything past the last day of the month except for the initial data on the 1st at 0:00. In this example I would like to delete everything below row #4.

Any help is greatly appreciated.

So far I have tried the following and I can filter the data on the date column, but I have not been successful at combining the filter for the second column.

Sub Delete_Row_Cell_Contains_Text()
    Dim Row As Long
    Dim i As Long
    Row = 15
    For i = Row To 1 Step -1
        If Cells(i, 1) = "12" Then
            Rows(i).Delete
        End If
    Next
End Sub

CodePudding user response：

Try something like this:

Sub Delete_Row_Cell_Contains_Text()
    
    Dim i As Long, ws As Worksheet, currMonth As Long, dt As Date
    Dim keepDateTime
    
    currMonth = Month(Date)
    keepDateTime = DateSerial(Year(Date), currMonth, 1) 'date/time row to keep
    
    Set ws = ActiveSheet
    For i = ws.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 'from last date up...
        dt = ws.Cells(i, 1).Value
        If Month(dt) <> currMonth - 1 Then 'if not this month-1
            'if not the first of this month at midnight
            If dt   ws.Cells(i, 2).Value <> keepDateTime Then
                ws.Rows(i).Delete
            End If
        End If
    Next
End Sub