formula in VBA to keep or remove

I need a formula that will look at the date in column B in my active sheet and then keep any data where the date is Today, Today +3 or Today +5 [excluding weekends] and delete all other rows

So today it would keep 22/8, 27/8 and 29/8 on Monday it would keep 25/8, 28/8 and 1/9

Could an expert provide me with VBA code to do this please.

Thanks in advance
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ButlerTechnologyCommented:
Here's a short routine that should work
Public Sub DateRemoveRow()
Range("A2").Activate

Do
  If Abs(DateDiff("d", Now(), ActiveCell.Range("B1"))) > 5 Then
    ActiveCell.EntireRow.Delete
  Else
    ActiveCell.Offset(1, 0).Activate
  End If
Loop Until IsEmpty(ActiveCell)

Range("A2").Activate
End Sub

Open in new window

I am assuming that the first row has headings and shouldn't be touched.  If this is not the case, you can adjust the starting position.
Tom
0
Rgonzo1971Commented:
Hi,

pls try

Sub macro1()
Set WsF = Application.WorksheetFunction
today = Date * 1
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For Idx = LastRow To 2 Step -1
    If Range("B" & Idx) <> today And Range("B" & Idx) <> WsF.WorkDay(today, 3) _
            And Range("B" & Idx) <> WsF.WorkDay(today, 5) Then
       Range("B" & Idx).EntireRow.Delete
    End If
Next
End Sub

Open in new window

EDIT Corrected code
Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
perfect Thanks Rgonzo I figured you might provide the answer.
0
JagwarmanAuthor Commented:
Thanks for trying ButlerTechnology unfortunately your code included 23/24/25 and 26th which I did not want to include.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.