Delete Rows, Based on Date

  • I have a macro that I would like to edit. Currently the code executes to the point where I have to intervene manually to complete the task. Is there VBA that I can place into the macro that will, after identifying the specific column, delete all rows that contain a date greater than <today>? The macro would have already performed the sort of <date> in ascending order. The actual beginning row number of rows to be deleted will vary depending on the amount of entries for any given date of worksheet generation.

  • Re: Delete Rows, Based on Date


    Hi dave


    see this code...this should help you out



    this is set for Col D..you may change range references to suit your requirements


    HTH


    pangolin

  • Re: Delete Rows, Based on Date


    Pangolin:


    Thanks, this works great when I apply it to a new workbook. I have not been able to get it to run within my macro. Where should I insert the code you provided?


    Code
    Sub OutstandingOrders()
  • Re: Delete Rows, Based on Date


    Copy pangolin code and add a button or call in from the other code depends on how You use the code You had.
    - or -
    Copy and paste
    [vba]
    Dim endrow As Integer
    On Error Resume Next
    endrow = Sheets("sheet1").Range("D65536").End(xlUp).Row
    For i = endrow To 1 Step -1
    tdate = Cells(i, 4).Value
    If IsDate(tdate) = True And tdate > Date Then
    Cells(i, 4).EntireRow.Delete
    End If
    Next i
    [/vba]
    Over the code You have between
    [vba]
    Sub OutstandingOrders()
    '---- new code starts here


    ' --- new code in here


    '---- new code ends here
    End Sub
    [/vba]
    This assumes two things


    1 - You take a back Up (copy) of Your old codes just in case, and a back up copy of Your WorkBook just in case


    2 - You have nothing else in that section of code ie You only want the delete stuff


    jiuk

  • Re: Delete Rows, Based on Date


    Thanks, Jack.


    I made two attempts at placing the code, one between Sub Outstanding Orders() and the original macro code, and the other after the original macro code. The original macro code still runs in either instance, however, not the code provided by pangolin. Sorry, I have not been successful using the "Code tag" otherwise I could present an example.

  • Re: Delete Rows, Based on Date


    Jack, Pangolin:


    I get it now.


    Thanks, this is perfect.


    How do I mark this thread as successful?

  • Re: Delete Rows, Based on Date


    So far, so good. Thanks for all the help. I would like to add to this code so that rows with Column C that are blank, i.e., contain no date are deleted.

  • Re: Delete Rows, Based on Date


    Dave196
    Member


    Please do not make the post as solved - plays arround and messes up the search engine on OzGrid, besides give me a lot more work to do


    Cheers buddy - oh just leave the feed with a thank You to whom helps solve the questions and say what You need to


    jiuk

  • Re: Delete Rows, Based on Date


    What should be the macro to take 'Date' from a user (code should prompt user to give specific date) or read date in Cell O2, and delete entire ROWS from Column O, where similar date is found, including Row 2 where the code read the date from cell O2, or taken from a user through prompt.

  • Re: Delete Rows, Based on Date


    [h=2]Delete Rows, Based on Date[/h][INDENT]I have a macro that I would like to edit. Currently the code executes to the point where I have to intervene manually to complete the task. Is there VBA that I can place into the macro that will, after identifying the specific column, delete all rows smaller or equal with yesterday 06:00 AM and greater than today 06:00. I tried here something, but it´s not what i would to have. Could you help me please ? Thanks in advance


    Sub DeleteRowBasedOnDateandTimeRange()


    Dim RowToTest As Long
    Dim ws As Worksheet
    Dim dts As Long, dte As Long, tm As String



    dts = Date - 1
    dte = Date
    tm = #6:00:00 AM#



    For Each ws In ThisWorkbook.Sheets



    For RowToTest = ws.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1



    With ws.Cells(RowToTest, 1)
    If .Value <= dts & tm _
    And .Value >= dte & tm _
    Then _
    ws.Rows(RowToTest).EntireRow.Delete

    End With



    Next RowToTest



    Next



    End Sub


    [/INDENT]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!