Solved

Delete rows based on X "network" days old

Posted on 2014-11-04
8
149 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

912 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

21 Experts available now in Live!

Get 1:1 Help Now