[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 2
1 Solution
 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now