Delete Rows but depends on day off week

Jagwarman
Jagwarman used Ask the Experts™
on
Can an expert provide me with VBA that will delete rows depending on day of week.

So:

if today is Monday and date in Col G is less than today -3 Delete row

But

if today is Tuesday to Friday and date in Col G is less than today -1 delete row

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Rgonzo1971
Top Expert 2016

Commented:
HI,

pls try

Sub macro()

For Idx = Range("G" & Cells.Rows.Count).End(xlUp).Row To 2 Step -1
    If Cells(Idx, "G") < WorksheetFunction.WorkDay(Date, -1) Then
        Cells(Idx, "G").EntireRow.Delete
    End If
Next
End Sub

Open in new window

Regards
Jagwarman

Author

Commented:
thanks Rgonzo. Works brilliant for today but I will need to test it on Monday :-)

Although I am sure it will work fine
Jagwarman

Author

Commented:
Hi Rgonzo,

of course it worked :-)

but

there is always something. for some reason in the file today the users have left the date in several of the cells blank and from what I can tell the code has deleted those rows. Can this be amended to say if blank ignore.

Hope you are ok with that or I would be happy to do a re-post to get you extra points
Top Expert 2016
Commented:
then try

Sub macro()

For Idx = Range("G" & Cells.Rows.Count).End(xlUp).Row To 2 Step -1
    If Cells(Idx, "G") < WorksheetFunction.WorkDay(Date, -1) and Cells(Idx, "G") <> "" Then
        Cells(Idx, "G").EntireRow.Delete
    End If
Next
End Sub

Open in new window

Jagwarman

Author

Commented:
Excellent many thanks Rgonzo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial