formula in VBA to keep or remove

Posted on 2014-08-22
Last Modified: 2014-08-22
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
Question by:Jagwarman
    LVL 6

    Expert Comment

    Here's a short routine that should work
    Public Sub DateRemoveRow()
      If Abs(DateDiff("d", Now(), ActiveCell.Range("B1"))) > 5 Then
        ActiveCell.Offset(1, 0).Activate
      End If
    Loop Until IsEmpty(ActiveCell)
    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.
    LVL 47

    Accepted Solution


    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
    End Sub

    Open in new window

    EDIT Corrected code

    Author Closing Comment

    perfect Thanks Rgonzo I figured you might provide the answer.

    Author Comment

    Thanks for trying ButlerTechnology unfortunately your code included 23/24/25 and 26th which I did not want to include.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now