Macro-Delete/clear contents on other columns if past today's date

[ld1414]

Hello there,

Looking for assistance. I'm attempting to see if a macro can be created to clear encumbrances on our account reconciliations. On this sheet, we have column F which contains biweekly payroll dates. Then Column G, H, I are calculations made by our team of employee's salaries, ERE (benefits), and tuition.

What I would like this macro to do is scan column F to see if a cell contains a date is less than today's date. If the date is less than today's date, then I would like it to clear the remainder of column G-I on that row.

Cannot have rows deleted since this is other information on columns A-E.

Thank you for any assistance.

 

[My Aswer Is This]

You said:

On this sheet​

What is the sheet name?

So If F5 is less then today
Clear contents of column G H and I of row 5
Is that what you want

And we start on row(2) of column F and continue all the way down row till there is no more data on column F

 

[ld1414]

You said:

On this sheet​

What is the sheet name?

So If F5 is less then today
Clear contents of column G H and I of row 5
Is that what you want

And we start on row(2) of column F and continue all the way down row till there is no more data on column F

Hello there,

Sorry I attempted to post a attachment, but couldn't find a easy way to do so. You are correct. If "F5" has a date like 2/28/18 and today is 3/5/18, then I want the macro to clear contents on G5, H5, I5.

Also I should add on that other rows have totals on the bottom. The F column is left blank, but G, H, I have totals of all activity above. I hope that's not an issue.

 

[My Aswer Is This]

If the bottom rows. Not sure what that means have formulas adding up the values in columns G H and I
And we clear some values in columns G H and I those formulas in those bottom rows will show the new results since those values in the rows will be cleared.
Is that a problem?

And when say if date in column F is less then today.
Are any of those rows empty? Because a empty cell is considered less then today unless I tell the script to ignore empty cells.

 

[ld1414]

Hello there,

Here is a shareable link on google that might help explain. On the image:

Row 1-titles of columns.
Row 2-5-blank. No dates. I change my mind. It should not matter if your macro clears this as these should be blank anyways.
Row 6-18-Dates that are previous to today's date. The macro should clear anything on columns G,H,I.
Row 19-31-Dates ahead of current date. Should not clear G-I until current date pasts.
Row 311. No date, but rather it says "salary". Then autosums of everything above in each column.

Link:
https://drive.google.com/file/d/1yfn33Bk6c4UBvPKEUBboCLV40-g6Pd6J/view?usp=sharing

 

[ld1414]

To answer your previous question.

If the bottom rows. Not sure what that means have formulas adding up the values in columns G H and I
And we clear some values in columns G H and I those formulas in those bottom rows will show the new results since those values in the rows will be cleared.
Is that a problem?

​

That's ok. These columns are basically a forecast for a budget. As time moves along we manually remove them. We have 190 files and I believe a macro can do this automatically for the team. It would save probably a good 4 hours a week since there is that many accounts.

And when say if date in column F is less then today.
Are any of those rows empty? Because a empty cell is considered less then today unless I tell the script to ignore empty cells.

​

Yes as described above. Some have text such as the "salary" described on row 311. I can remove this or move to another column if it makes life easier. Let me know and thank you

 

[My Aswer Is This]

Try this:

I assume you will only want this to be done on the active sheet where you run this script from.

Sub Check_Dtates()
'Modified 3-5-18 4:25 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
        For i = 2 To Lastrow
            If Cells(i, "F").Value < Date Then Cells(i, "G").Resize(, 3).ClearContents
            Next
Application.ScreenUpdating = True
End Sub

 

[ld1414]

Worked to perfection! Thank you sir

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

If your wanting to do this on more then one sheet in your workbook we could write the script to do all sheets at once.

 

[ld1414]

Will definitely be back. Thank you!