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

 

[DanteAmor]

Welcome to the MrExcel Message Board!

Try this:

Sub Delete_Dates()
  Dim i As Long, j As Long
  
  Application.ScreenUpdating = False
  For i = 4 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
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Delete_Dates) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[mdeni0528]

Thank you so much, this worked! I saved it as a macro-enabled workbook but I'm not getting the message that says do you want to enable macros. Is that weird? I was hoping that this would run automatically when the user opens the workbook. I really appreciate your help!

 

[DanteAmor]

To have the macro run automatically when you open the workbook, rename the macro to auto_open

Sub auto_open()
  Dim i As Long, j As Long
  
  Application.ScreenUpdating = False
  For i = 4 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
End Sub

 

[mdeni0528]

Also, I added headers in 5 rows above the example that I originally sent you, so now it's deleting my headers. The start and end date headers are from B8:AQ8 and the start and end dates are found in cells B9:AQ265. Sorry!

 

[DanteAmor]

headers are from B8:AQ8 and the start and end dates are found in cells B9:AQ265.

Now, try this:

Sub auto_open()
  Dim i As Long, j As Long
  
  Application.ScreenUpdating = False
  For i = 9 To Range("A" & Rows.Count).End(3).Row
    For j = 8 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
End Sub

 

[mdeni0528]

Now, try this:

Sub auto_open()
  Dim i As Long, j As Long
 
  Application.ScreenUpdating = False
  For i = 9 To Range("A" & Rows.Count).End(3).Row
    For j = 8 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
End Sub

Okay, I copied the latest in the Module and it's not deleting any dates now. I'm sorry!

 

[DanteAmor]

Change this line:

For j = 8 To Cells(i, Columns.Count).End(1).Column Step 2

For this line:

For j = 3 To Cells(i, Columns.Count).End(1).Column Step 2

 

[mdeni0528]

YAY!! It worked and I'm so thankful for your help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.