Remove cell contents based on date - VBA

[JedZedOne]

Hi everyone,

Basically, I have a list of names and reasons for people being on a list. Plus the date their names were added to the list.

What I'm wanting is for the line with their info to be removed after 7 days when we open the spreadsheet.

Referring to the image, lets take row 2-4. Their names were added >= 7 days ago.
So A2:C4 should be automatically removed cleared when we open the spreadsheet.

Column D is automatically generated based on the date in Column C. So if you clear C it will clear D.

My knowledge of VBA is extremely limited. But I've tried to piece a few things together. Which isn't quite working the way I want it too. See below.

Sub removedate()
Dim rw As Long
With ActiveSheet
On Error Resume Next
rw = Application.WorksheetFunction.Match(CDbl(Date), .Range("D:D").Value2, 0)
On Error GoTo 0
If rw = 0 Then
rw = Application.WorksheetFunction.Match(CDbl(Date), .Range("D:D").Value2, 1)
End If
.Range("A1:C" & rw).ClearContents
End With

End Sub

Hope this makes sense. Any assistance is always appreciated.

 

[kevin9999]

the line with their info to be removed after 7 days when we open the spreadsheet.

For this you'll need to use Workbook_Open event code. It is important to put the code below in the correct place. In the VBA Editor (I assume you know how to get there given the code you've already produced) double-click on ThisWorkbook in the project explorer window (top left of the screen). When a blank window opens on the right of the screen, copy the code below into it, save the file as macro-enabled, then reopen it. Just change the sheet name in the code to whatever your sheet name is first.

Private Sub Workbook_Open()
    Application.EnableEvents = False
    Dim ws As Worksheet, LRow As Long, i As Long
    Set ws = Worksheets("Sheet1")               '<-- *** Change to actual sheet name ***
    LRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
    For i = LRow To 2 Step -1
        If ws.Cells(i, 3) <= (Date - 7) Then ws.Rows(i).EntireRow.Delete
    Next i
    Application.EnableEvents = True
End Sub

 

[JedZedOne]

You're a bloody legend! Thank you for your help

 

[kevin9999]

You're a bloody legend! Thank you for your help

Happy to help, and thanks for the feedback