Results 1 to 9 of 9

Macro - delete row if column has certain date

This is a discussion on Macro - delete row if column has certain date within the Excel Questions forums, part of the Question Forums category; Hey All, I am just a beginner at this and require some help. I generate a report in excel of ...

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    2

    Question Macro - delete row if column has certain date

    Hey All,

    I am just a beginner at this and require some help.

    I generate a report in excel of about 63000 rows (this differs) there are several columns with dates, I wish to remove all the rows with a date before and after certain dates.

    It seems I would need a loop for that, but it is unclear to me how this works, so far I have this:

    Sub TestMacro2()
    Columns("D:E").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("L:M").Select
    Selection.NumberFormat = "m/d/yyyy"
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Rows("1:1").Select
    Selection.AutoFilter
    Range("$D").Select
    For Each cell In Selection
    If Date < 1 - 3 - 2009 Or Date > 2 - 3 - 2010 Then
    cell.EntireRow.Delete shift:=xlUp
    End If
    Range("$D").Select
    Next cell
    End
    End Sub

    can anyone please help me better understand this?

  2. #2
    Board Regular pboltonchina's Avatar
    Join Date
    Apr 2008
    Location
    England
    Posts
    1,092

    Default Re: Macro - delete row if column has certain date

    Is the macro doing what you want it to do or not? If not, what doesn't it do that you would like it to do. The best way to understand what's happening is to open the VBA window and reduce it's size so you can see your spreadsheet, click your mouse in the VBA window at the start of the macro and then keep pressing F8. This scrolls through the macro one command at a time with the command highlighted so you can see where you are in the macro and see what it's doing on your sheet.

    HTH

    Regards

    Paul

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,827

    Default Re: Macro - delete row if column has certain date

    There's a couple problems...

    The biggest is this

    If Date < 1 - 3 - 2009 Or Date > 2 - 3 - 2010 Then

    In VBA, simply writing DATE refers to TODAY'S Date. Not the date that is in the cell you are referring to. If you want to use the date that is in a cell, refer to that cell.
    Also, this
    1 - 3 - 2009
    is not the correct way to write a date in VBA. VBA is reading that like
    1 MINUS 3 MINUS 2009.
    Try #1/3/2009#

    So that line should be written like
    If cell.Value < #1/3/2009# Or Cell.Value > #2/3/2010# Then

    there's another problem, but I'll let you digest this first, while I write out an explaination of the next problem..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,827

    Default Re: Macro - delete row if column has certain date

    The next problem is this.
    doing a For Each cell in Range... loop goes top to bottom.
    So say the range is A1:A100, it's going to start in A1, then A2, then A3 etc...

    So say it met the criteria on cell A4 for example.
    A4 was between 1/3/09 and 2/3/10.
    so row 4 get's deleted.
    so far so good.

    here's the problem.
    Whatever WAS in A5, has now shifted to A4.
    But the next loop moves on to test A5.
    A5 now holds whatever WAS in A6.
    So the value that was originally in A5 essencially get's skipped.

    You have to write your code to go backwards.
    A100 then A99, then A98 etc..

    It's fairly simple but requires a very different method of looping.

    check back in a minute and I'll post something..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,827

    Default Re: Macro - delete row if column has certain date

    Try

    Code:
    Sub test()
    Range("D:E,L:M").NumberFormat = "m/d/yyyy"
    Rows(2).Select
    ActiveWindow.FreezePanes = True
    Rows(1).AutoFilter
     
    'Create a variable that = the last used row in column D
    LR = Cells(Rows.Count, "D").End(xlUp).Row
     
    'Create a looping variable # to go backwards from the last used Row# to 1
    For i = LR To 1 Step -1
        If Cells(i, "D").Value < #1/3/2009# Or Cells(i, "D").Value > #2/3/2010# Then
            Rows(i).EntireRow.Delete shift:=xlUp
        End If
    Next i
    End Sub
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    New Member
    Join Date
    Feb 2010
    Posts
    2

    Default Re: Macro - delete row if column has certain date

    Ah it seems I have a lot to learn, as said I am completely new to this, I have some experience programming on graphic calculators in high school but that's about it

    I understood the shifting of rows after you delete one, zo I included the Delete shift:=xlUp statement (at least I thought that caused the macro to go back up one cell)

    The thing about the Date & the statement, that was just stupid of me, I should have read those statements better, my appologies.

    I just ran the macro you provided and it works splendidly except for my first row (which contains tekst) is also deleted, any statement I can include to prevent that?

  7. #7
    Board Regular #BoB#'s Avatar
    Join Date
    May 2008
    Location
    Mumbai, India
    Posts
    301

    Default Re: Macro - delete row if column has certain date

    Probably you'd want to run the given code upto your second row (starting from last row)...

    Like this:

    For i = LR To 2 Step -1


    Just replace 1 by 2 in the For statement
    #BoB#

    ~What you do today will decide your tomorrow...

  8. #8
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,037

    Default Re: Macro - delete row if column has certain date

    Another way is to filter out all the rows you want to delete; and then delete them in one big hit. It tends to be considerably quicker than looping, particularly on big ranges.

    See example 5 in the attached article - which demonstrated deleting rows using an autofilter with date range criterion. Article here

  9. #9
    Board Regular
    Join Date
    Mar 2005
    Location
    Arkansas, USA
    Posts
    115

    Default Re: Macro - delete row if column has certain date

    Jon von der Heyden,
    This is what I need...I have around 20,000 rows that I would like to delete the rows where the date is 90 days earlier than today's date. (if it's less than 90 days, then it's not deleted). When I click on your link to read the article, the page timesout and can't be loaded. Anything I can do to be able to read this article?

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com