Delete all dates greater than a date I specify in different worksheet but same workbook.

[rharri1972]

Hello! Hoping to get help from anyone gracious enough to help.
Here is my dilemma.
I have two worksheets in a workbook.
"INSTRUCTIONS" and "REPORT"
I will specify a certain date in INSTRUCTIONS
when i hit the button to control the macro's I am needing code to look through column D in "REPORTS" starting at ROW4 to the end and if the date in column D is greater than specified in INSTRUCTIONS Q2 then delete that entire row.
Example... date specified is 12/6/2021... I have dates going all the way to March of 2022 in Column D on worksheet ("REPORT"). I need any date greater than 12/6/2021 to be deleted. Well that entire row deleted. I know i have talked in circles and I apologize.
Can or will anyone help please?

 

[rollis13]

This could be a solution for you. The macro can be pasted in a standard module and then be associated to your control button.

Option Explicit
Sub DeleteRowsByDate()
    Dim lr     As Long
    Dim rw     As Long
    With Sheets("REPORT")
        lr = .Range("D" & Rows.Count).End(xlUp).Row
        For rw = lr To 4 Step -1
            If .Cells(rw, "D").Value > Sheets("INSTRUCTIONS").Range("Q2").Value Then
                .Rows(rw).Delete Shift:=xlUp
            End If
        Next rw
    End With
End Sub

 

[GWteB]

Hi & welcome to MrExcel!

Another macro to try ...

Sub rharri1972()
    Dim Dt As Long, Lr As Long
    Dt = Sheets("instructions").Range("Q2").Value2
    With Sheets("reports")
        Lr = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("D3:D" & Lr).AutoFilter Field:=1, Criteria1:=">" & Dt
        .Range("D4:D" & Lr).SpecialCells(xlVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

 

[rharri1972]

This could be a solution for you. The macro can be pasted in a standard module and then be associated to your control button.

Option Explicit
Sub DeleteRowsByDate()
    Dim lr     As Long
    Dim rw     As Long
    With Sheets("REPORT")
        lr = .Range("D" & Rows.Count).End(xlUp).Row
        For rw = lr To 4 Step -1
            If .Cells(rw, "D").Value > Sheets("INSTRUCTIONS").Range("Q2").Value Then
                .Rows(rw).Delete Shift:=xlUp
            End If
        Next rw
    End With
End Sub

This worked! Thank you so much for your help!!!

 

[rollis13]

Glad having been of some helpbut I would like to point out that GWteB 's macro is faster if you have to handle a large amount of rows.

 

[rharri1972]

Glad having been of some helpbut I would like to point out that GWteB 's macro is faster if you have to handle a large amount of rows.

Thank you for that info however, my data should not go past 2000 rows ever so i think we will be ok.