VBA Remove rows based on date

[osvald]

Hi!

I want to delete rows that has a date in column H that is 7 days older than today.
Preferably I'd like the macro to run when someone open the worksheet (possible with Private Sub Workbook_Open()?).
So that it automaticly wipe away rows that's not relevant any more.

Is this possible? Can anyone help me with this?
I'd greatly appreciate it.

 

[osvald]

I've tried this code and it almost does the work. But it removes empty cells also, not just the cells with date 7 days prior today.
Any suggestions?

Sub Delete_Prior()
     
    Dim i As Long
     
    For i = Range("J" & Rows.Count).End(xlUp).Row To 9 Step -1
        If Range("J" & i) < Date - 7 Then
            Range("J" & i).EntireRow.Delete
        End If
    Next i
End Sub

(The column is J not H as stated in the thread start)

 

[VoG]

Try

Sub Delete_Prior()
     
    Dim i As Long
     
    For i = Range("J" & Rows.Count).End(xlUp).Row To 9 Step -1
        If Range("J" & i).Value <> "" And Range("J" & i).Value < Date - 7 Then
            Range("J" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[osvald]

Thank you very much VoG! That works like a charm