Solved

Delete rows based on X "network" days old

Posted on 2014-11-04
8
151 Views
Last Modified: 2014-11-04
Hi,

I have a spreadsheet with a date in column G.

I want to delete every row where that date is less than 9 network days old.

In other words, what I want left are only rows where that date is more than 8 network days old.

Thanks in advance,

swjtx99
0
Comment
Question by:swjtx99
  • 4
  • 3
8 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40422856
Deleting rows older than 8 calendar days is simple enough but what is a "network day"?
0
 

Author Comment

by:swjtx99
ID: 40422881
Hi Russ,

Network day is a Workday. I do not want the 8 days to include weekends.

Thanks,

swjtx99
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40422891
Ah, gotcha.

You can use the NETWORKDAYS() Excel function for this.

=NETWORKDAYS(A1, TODAY()) -- where cell A1 contains the date you want to check against.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:swjtx99
ID: 40422906
Hi Russ,

Yes but I need a VBA solution as it's a step I want done in a macro.

Thanks,

swjtx99
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40422917
Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
Dim d As Long, dCount As Long
    For d = StartDate To EndDate
        If WeekDay(d, vbMonday) < 6 Then
            dCount = dCount + 1
        End If
    Next d
    GetWorkDays = dCount
End Function

Open in new window


The only thing is this function won't count holidays
0
 

Author Comment

by:swjtx99
ID: 40422930
Hi Russ,

Looks like it is counting days but I don't see where it's looking at Column G or deleting anything?

I found this on a different thread which looks like what I'm trying to do except it's not working and I haven't figured out why.

Dim lstRow As Long, Idx As Long
lstRow = Range("G" & Cells.Rows.Count).End(xlUp).Row
For Idx = lstRow To 1 Step -1 ' change 1 to the first row with dates
    If Cells(Idx, 1) < Application.WorksheetFunction.WorkDay(Date, -8) Then
        Cells(Idx, 1).EntireRow.Delete
    End If

Thanks,

swjtx99
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40422997
Two things:
1) The code you just posted isn't using the correct argument for today's date ("Date" instead of "Now()") in order to calculate the number of net workdays.  
2) The code is using column G to determine the number of rows, but is testing cells in column A ( Cells(Idx, 1) ).
Revise the code to this:
    Dim lstRow As Long, Idx As Long
    lstRow = Range("G" & Cells.Rows.Count).End(xlUp).Row
    For Idx = lstRow To 1 Step -1 ' change 1 to the first row with dates
        If Cells(Idx, 7) < Application.WorksheetFunction.WorkDay(Now(), -8) Then
            Cells(Idx, 7).EntireRow.Delete
        End If
    Next Idx

Open in new window


Regards,
-Glenn
0
 

Author Closing Comment

by:swjtx99
ID: 40423002
Hi Glenn,

Thanks! I was almost there realizing it was looking at Column A. Appreciate the help!

Regards,

swjtx99
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 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