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
Visual Basic ClassicMicrosoft ExcelMicrosoft ApplicationsVBA

Avatar of undefined
Last Comment
Jagwarman

8/22/2022 - Mon
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
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
Jagwarman

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jagwarman

ASKER
Excellent many thanks Rgonzo