+ Reply to Thread
Results 1 to 4 of 4

Delete Rows with dates older than previous Friday

  1. #1
    Registered User
    Join Date
    09-05-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2013
    Posts
    71

    Delete Rows with dates older than previous Friday

    Hello,
    I'm fairly new to VBA and currently automating a weekly report.

    I extract this report from a sheet within a workbook managed by another department, then clean it up to be used as a source file for a larger report. Normally, I run this report on Fridays, but occasionally I'll do it over the weekend or Monday. All rows with a date (column H) older than the previous Friday must be deleted. For example, today is Friday, 5/31/13 so I need to delete all rows with a date greater than Friday, 5/24/13.

    Although I know code that will delete rows exactly 7 days back, but I don't know how to create code that will use a date range and will pass this on to someone in an admin role after finishing the automation. Attached is a sample of the report.

    For any who take the time to help me, thank you in advance.

    DELETE ROWS BY DATE.xlsb

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Delete Rows with dates older than previous Friday

    here you go

    Please Login or Register  to view this content.
    Last edited by scott.s.fower; 05-31-2013 at 07:37 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Delete Rows with dates older than previous Friday

    i was a bit confused by your post because you say
    older than the previous Friday must be deleted
    then you say
    today is Friday, 5/31/13 so I need to delete all rows with a date greater than Friday, 5/24/13.
    a date greater than means more current, but older means further in the past

    either way just change the
    Please Login or Register  to view this content.
    line from < to > to suit your needs

    also your example only went to 5/23/13 so there were no dates more recent anyway (it deletes no rows >, also it deletes all lines if you say <) I extended the dates out so i could see if it worked and the code works either direction

  4. #4
    Registered User
    Join Date
    09-05-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2013
    Posts
    71

    Re: Delete Rows with dates older than previous Friday

    It Works perfectly!! The only thing is that it keeps deleting my top two rows. My data for that column actually starts on cell H3, H2 is the header and H1 contains a button to run the macro. I altered it per below and it stalls out now. Any idea what I'm doing wrong?

    Sub Clean_Dates()
    Dim Firstrow As Long
    Dim LastRow As Long
    Dim Lrow As Long

    Application.Calculation = xlManual
    Application.ScreenUpdating = False

    Range("H3").Select
    With ActiveSheet
    Firstrow = Range("H3").Row
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    For Lrow = LastRow To Firstrow Step -1
    With .Cells(Lrow, "H")
    If Not IsError(.Value) Then
    d = Date - WorksheetFunction.Weekday(Date) - 1

    ' A date greater than means more current, but older means further in the past either way just change the line from < to > to suit your needs
    If .Value < Date - WorksheetFunction.Weekday(Date) - 1 Then .EntireRow.Delete
    End If
    End With
    Next Lrow
    End With
    Delete_Unwanted_Dates
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1