How to automatically delete cell contents in excel after a week and after a day

[malotic]

Good day! I would like to have a code that deletes the contents in B7 every 12AM sunday and deletes the range M3 to M23 every day at midnight. I dont have any knowledge on macros. Thank you

 

[Jaafar Tribak]

You will need to use Windows Task Scheduler to automatically open the excel file and run the required macros.

The above WTS link is for Win10. If you run a different Windows version, do a new Google search.

 

[malotic]

I actually have a macro code for M3:M23 i just dont know what code and where to the code that removes B7 also but it is done every week. If deleting the content in B7 can't be done using a macro, HOW ABOUT deleting the content in B7 every after 5 days

Private Sub Workbook_Open()
    If Sheets("Aux").Range("A1").Value < Date Then
        Sheets("CAPITAL").Range("M3:M23").ClearContents
    End If
    Sheets("Aux").Range("A1").Value = Date
End Sub

 

[Jaafar Tribak]

Would this work for you ?

Private Sub Workbook_Open()

    If Sheets("Aux").Range("A1").Value < Date Then
        Sheets("CAPITAL").Range("M3:M23").ClearContents
    End If
    Sheets("Aux").Range("A1").Value = Date
    
    If Weekday(Date, vbSunday) = 1 Then
        Sheets("CAPITAL").Range("B7").ClearContents
    End If

End Sub

 

[malotic]

Thank you very much! Don't know how to test this without actually waiting for sunday midnight, so i'll get back to you

 

[malotic]

I just copied the code somewhere here in mr excel so i just wanna ask if the last part actually ends with "end if .. end sub" without these " Sheets("Aux").Range("A1").Value = Date"

 If Weekday(Date, vbSunday) = 1 Then
        Sheets("CAPITAL").Range("B7").ClearContents
    End If

End Sub

 

[Jaafar Tribak]

Thank you very much! Don't know how to test this without actually waiting for sunday midnight, so i'll get back to you

FYI, the code won't run until you first manually open the workbook. The code won't run by itself on sunday midnight even if the workbook is left is open

 

[Jaafar Tribak]

I just copied the code somewhere here in mr excel so i just wanna ask if the last part actually ends with "end if .. end sub" without these " Sheets("Aux").Range("A1").Value = Date"

 If Weekday(Date, vbSunday) = 1 Then
        Sheets("CAPITAL").Range("B7").ClearContents
    End If

End Sub

Just remove the line Sheets("Aux").Range("A1").Value = Date and leave the rest of the code in post#4 as is.

 

[malotic]

FYI, the code won't run until you first manually open the workbook. The code won't run by itself on sunday midnight even if the workbook is left is open

That's fine by me. Thanks again

 

[malotic]

Would this work for you ?

Private Sub Workbook_Open()

    If Sheets("Aux").Range("A1").Value < Date Then
        Sheets("CAPITAL").Range("M3:M23").ClearContents
    End If
    Sheets("Aux").Range("A1").Value = Date
   
    If Weekday(Date, vbSunday) = 1 Then
        Sheets("CAPITAL").Range("B7").ClearContents
    End If

End Sub

WORKS GREAT! Manually changed the date in my pc a few times and saw that the B7 is removed every sunday only. THANK YOU VERY MUCH