VBA Clear Contents of Cells Less than Today's Date

mdeni0528

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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
Capture_VBA Help Example.PNG
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Try this:

VBA Code:
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.
 
Solution

mdeni0528

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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!

1628778979479.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
To have the macro run automatically when you open the workbook, rename the macro to auto_open

VBA Code:
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

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
headers are from B8:AQ8 and the start and end dates are found in cells B9:AQ265.

Now, try this:

VBA Code:
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

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Now, try this:

VBA Code:
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!

1628786863871.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
Change this line:

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

For this line:
VBA Code:
For j = 3 To Cells(i, Columns.Count).End(1).Column Step 2
 

Forum statistics

Threads
1,144,390
Messages
5,724,074
Members
422,535
Latest member
navjeet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top