VBA Script that deletes all rows of data NOT specified in reference cells

[PagsG]

Looking for help. The column below shows dates in the mm/dd/yyyy format, and can cover a range of time spanning several years. I want to write an Excel VBA script to identify the month specified in a different worksheet (Titled "REF"), and delete all rows that are not within that month/year.

For example, I'm measuring August, 2022 right now - and only want to keep the rows of data that begin with 8, and end with 2022.

REF worksheet example:

Thanks!

 

[jasonb75]

Perhaps something like this?
I've assumed that the sheet with the rows to delete will already be selected when you run the code.

Note that I've had to test with UK format dates (Day/Month/Year) but as far as I can see it should work with any format as long as the dates are valid.

Option Explicit
Sub test()
Dim lRow As Long, refDate As String
lRow = Cells(Rows.Count, 35).End(xlUp).Row
With Worksheets("Ref")
    refDate = .Range("A2").Value & .Range("B2").Value
End With
Do Until lRow = 1
    If Not Format(Cells(lRow, 35), "myyyy") = refDate Then Rows(lRow).Delete
    lRow = lRow - 1
Loop

End Sub

 

[PagsG]

Perhaps something like this?
I've assumed that the sheet with the rows to delete will already be selected when you run the code.

Note that I've had to test with UK format dates (Day/Month/Year) but as far as I can see it should work with any format as long as the dates are valid.

Option Explicit
Sub test()
Dim lRow As Long, refDate As String
lRow = Cells(Rows.Count, 35).End(xlUp).Row
With Worksheets("Ref")
    refDate = .Range("A2").Value & .Range("B2").Value
End With
Do Until lRow = 1
    If Not Format(Cells(lRow, 35), "myyyy") = refDate Then Rows(lRow).Delete
    lRow = lRow - 1
Loop

End Sub

Thank you Jason,

This worked perfectly. I appreciate the help!