Link to home
Start Free TrialLog in
Avatar of Jagwarman
Jagwarman

asked on

Delete Rows but depends on day off week

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
Avatar of Rgonzo1971
Rgonzo1971

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
Avatar of Jagwarman

ASKER

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

Although I am sure it will work fine
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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent many thanks Rgonzo