In Excel add 180 days from yesterday's date and delete rest of the rows

Question

I have an Excel report that generates everyday that have dates that differ from anywhere to three days to two due to the uneven amount of calendar days in any given month. The report, in column F looks like this below. Today's date is 07/16/13 and the process generating the report is configured to show anything greater then 180 days so that we catch the right data.

I'd like to delete all rows that are not equal to 1/15/14. I don't know if there is a way to add another column with just 1/15 and then delete the rest? Any help or direction would be greatly appreciated.

We are now going into the file everyday and doing a sort by date and then delete. I'd like to automate it.

Thank you

Have you tried using the filter to filter out the undesired rows? Or to copy the filtered data to a new sheet? — stenci, Jul 16 '13 at 18:13
I can filter out the dates manually, yes. I'm looking to automate this process though. — Jack Daniels, Jul 16 '13 at 19:50

score 1 · Answer 1 · edited May 23 '17 at 12:14

Use a loop to iterate through the rows and remove if less than your criteria. I referenced this thread for row deletion code: Delete a row in Excel VBA.

I'm assuming your dates are in Column A and start at Row 1.

Private Sub Remove_Rows()

Dim CutoffDate As Date
CutoffDate = Date + 180

Dim ws As Worksheet
Set ws = ActiveSheet

Dim RowCounter As Integer
RowCounter = 1

Do While ws.Cells(RowCounter, 1) <> ""
    If ws.Cells(RowCounter, 1) < CutoffDate Then
        ws.Rows(RowCounter).Delete
    End If
    RowCounter = RowCounter + 1
Loop

End Sub

In Excel add 180 days from yesterday's date and delete rest of the rows

1 Answers1