Announcement

Collapse
No announcement yet.

Delete Rows, Based on Date

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Delete Rows, Based on Date

    Hi dave

    see this code...this should help you out

    Code:
    Sub delrows()
    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
    End Sub
    this is set for Col D..you may change range references to suit your requirements

    HTH

    pangolin

    Comment


    • #3
      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()

      Comment


      • #4
        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

        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

        Over the code You have between

        Sub OutstandingOrders()
        '---- new code starts here

        ' --- new code in here

        '---- new code ends here
        End Sub

        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

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Delete Rows, Based on Date

            Do You need to assign the button You press again to run the codes

            jiuk

            Comment


            • #7
              Re: Delete Rows, Based on Date

              Jack, Pangolin:

              I get it now.

              Thanks, this is perfect.

              How do I mark this thread as successful?

              Comment


              • #8
                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.
                [CODE]Sub delrows()
                endrow = Sheets("Management Operations").Range("C65536").End(xlUp).Row
                For i = endrow To 1 Step -1
                tdate = Cells(i, 3).Value
                If IsDate(tdate) = True And tdate > Date Then
                Cells(i, 3).EntireRow.Delete

                End If
                Next i
                Cells.Select
                Cells.EntireColumn.AutoFit
                Range("A2").Select
                Columns("A:A").ColumnWidth = 10.43
                Columns("D:D").ColumnWidth = 26.57
                Columns("E:E").ColumnWidth = 5.57
                Columns("F:F").ColumnWidth = 7.14
                Columns("H:H").ColumnWidth = 9.43
                Columns("I:M").Select
                Selection.Delete Shift:=xlToLeft
                Range("I1").Select
                Columns("I:I").ColumnWidth = 11.86
                Columns("J:J").Select
                Selection.Delete Shift:=xlToLeft
                Selection.ColumnWidth = 15.71
                Columns("M:AB").Select
                Selection.Delete Shift:=xlToLeft
                ActiveWindow.SmallScroll ToRight:=-6
                Range("A2").Select
                Application.ScreenUpdating = True
                Application.Calculation = xlCalculationAutomatic

                End Sub
                Code:
                CODE

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11


                      Re: Delete Rows, Based on Date

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

                      Comment

                      Working...
                      X