VBA Clear Contents of Cells Less than Today's Date

[mdeni0528]

Hi!

I'm hoping you can help me with a VBA formula that clears the contents of dates in cells based on an end date that is less than today's date. My worksheet has a start date and an end date for multiple segments and these dates are associated with one another. The clear contents command needs to be triggered by the end date when it's less than today's date. The end date needs to be cleared from the worksheet as well as the start date associated with it. I have multiple columns of start dates and end dates, so I need to apply this command to multiple columns. I need a formula similar to the one below but for entire columns since this only applies to one cell and is not written correctly. My start and end dates that should be deleted are highlighted in red using a conditional format. Please help! I don't know VBA language at all! Thank you!

If Date > Range("C9").Value then Range("C9").ClearContents

If Range("B9")> isnotblank and Range("C9")Then Range("B9").ClearContents

 

[mdeni0528]

Hi Dante! I password protected my sheet and I'm getting this run-time error below. Do you know why? When I hit End, it goes away...

 

[DanteAmor]

I password protected my sheet

The sheet must be unprotected.
Change "abc" in the 2 lines of the macro your password:

Sub auto_open()
  Dim i As Long, j As Long
  Application.ScreenUpdating = False
  ActiveSheet.Unprotect "abc"
  For i = 9 To Range("A" & Rows.Count).End(3).Row
    For j = 3 To Cells(i, Columns.Count).End(1).Column Step 2
      If Date > Cells(i, j).Value Then
        Cells(i, j - 1).Resize(1, 2).Value = ""
      End If
    Next
  Next
  ActiveSheet.Protect "abc"
End Sub

 

[mdeni0528]

Awesome, thanks again!!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback

 

[mdeni0528]

Hi Dante!

I'm wanting my users to be able to use filters in the worksheet. Do I add to the code to allow for this capability? Thanks again!

 

[DanteAmor]

Change this line:

ActiveSheet.Protect "abc"

For this:

ActiveSheet.Protect "abc", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

 

[mdeni0528]

It worked! Thank you!

 

[mdeni0528]

Hi Dante,
Can you please help again with this formula? I have 3 worksheets in my workbook/file and the vba formula should only be clearing contents from the first worksheet not any of the others. However, it is clearing contents from the 2nd worksheet if I close the file when the 2nd worksheet is active. Can you please help? The formula is showing below Thank you!!

Sub auto_open()
Dim i As Long, j As Long
Application.ScreenUpdating = False
ActiveSheet.Unprotect "****"
For i = 9 To Range("A" & Rows.Count).End(3).Row
For j = 3 To Cells(i, Columns.Count).End(1).Column Step 2
If Date > Cells(i, j).Value Then
Cells(i, j - 1).Resize(1, 2).Value = ""
End If
Next
Next
ActiveSheet.Protect "****", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
End Sub

 

[DanteAmor]

I have 3 worksheets in my workbook/file and the vba formula should only be clearing contents from the first worksheet not any of the others.

Try this:

Sub auto_open()
  Dim i As Long, j As Long
  Dim sh As Worksheet
  
  Application.ScreenUpdating = False
  For Each sh In Sheets
    sh.Unprotect "***"
    For i = 9 To sh.Range("A" & Rows.Count).End(3).Row
      For j = 3 To sh.Cells(i, Columns.Count).End(1).Column Step 2
        If Date > sh.Cells(i, j).Value Then
          sh.Cells(i, j - 1).Resize(1, 2).Value = ""
        End If
      Next
    Next
    sh.Protect "***", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
  Next
End Sub

 

[mdeni0528]

Sorry, that didn't work. It still deleted dates on the 2nd worksheet.