Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Delete Rows, Based on Date

  1. #1
    Join Date
    3rd August 2004
    Location
    Canada
    Posts
    93

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th July 2004
    Posts
    1,373

    Re: Delete Rows, Based on Date

    Hi dave

    see this code...this should help you out

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    3rd August 2004
    Location
    Canada
    Posts
    93

    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?

    VB:
    Sub OutstandingOrders() 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd August 2004
    Location
    Canada
    Posts
    93

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Delete Rows, Based on Date

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

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd August 2004
    Location
    Canada
    Posts
    93

    Re: Delete Rows, Based on Date

    Jack, Pangolin:

    I get it now.

    Thanks, this is perfect.

    How do I mark this thread as successful?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    3rd August 2004
    Location
    Canada
    Posts
    93

    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
    VB:
    CODE 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    21st August 2013
    Posts
    19

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Delete Rows Based On Values In Rows Below
    By AlexanderLukas in forum EXCEL HELP
    Replies: 2
    Last Post: June 20th, 2008, 19:44
  2. Replies: 5
    Last Post: May 30th, 2008, 08:19
  3. Delete Rows Based On Date Column
    By Harley in forum EXCEL HELP
    Replies: 6
    Last Post: May 10th, 2008, 12:51
  4. Delete Rows Based On Criteria & X Rows Below
    By excelgrrl in forum EXCEL HELP
    Replies: 11
    Last Post: March 6th, 2008, 04:40
  5. Delete Rows Based On Inputted Date
    By jlsprink in forum EXCEL HELP
    Replies: 4
    Last Post: December 19th, 2006, 16:15

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