+ Reply to Thread
Results 1 to 8 of 8

Macro: Deleting Rows based on date value

  1. #1
    Registered User
    Join Date
    03-17-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Macro: Deleting Rows based on date value

    I'm currently exporting a daily report that lists due dates out to the next month. The dates are all listed in one column. I then sort the worksheet by date ascending and only keep the rows that contain the following criteria: dates in the past, today's date, and the next 5 consecutive dates after today's date. All other rows get deleted. I believe a macro would be the way to go, but my only issue would be correctly programming "the next 5 consecutive dates" condition. For example, say today was 8/30, and the next 5 dates that show are: 8/31, 9/4, 9/5, 9/6, and 9/7. My fear is that the macro I'd create would simply go out five calendar days to 9/4 and delete everything after 9/4 when in fact I need to go out to 9/7. And since this is a live table, dates are constantly changing day-to-day. Any input will be much appreciated.

    Will be used on a PC with Excel 2003.

    Sample worksheet to follow shortly....

    Ex1.xlsx
    Last edited by hreal34; 08-31-2012 at 01:05 AM.

  2. #2
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Macro: Deleting Rows based on date value

    If you want the next few days to stay from date 8/31 can't anything after 30/8 be left and everything before 8/30 deleted? Or if you have a date like 9/13 that one should also be deleted?

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Macro: Deleting Rows based on date value

    maybe something like
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-17-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro: Deleting Rows based on date value

    Yes, using today's date as reference, I want all prior dates to stay.
    And yes, anything well into the future, and beyond that 5-day threshold (i.e. 9/13) should be deleted.

    Since there won't be a delivery every day of the week, there will be "gaps" so to speak and certain dates won't be listed, so I don't want the macro to factor the missing dates into the 5 days.

  5. #5
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Macro: Deleting Rows based on date value

    I tryed. And it delete's too much - my code.
    nilem, I tryed yours and it didn't work for me.

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Macro: Deleting Rows based on date value

    Quote Originally Posted by stojko89 View Post
    nilem, I tryed yours and it didn't work for me.
    If I understand correctly, you need to remove lines with 09/08/2012 to 09/14/2012 (see example, Sheet2)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-17-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro: Deleting Rows based on date value

    Exactly right nilem. Your macro works like a charm.

    Question:
    We might have rare instances where Saturday dates pop up in the report. I assume WORKDAY wouldn't include those when the macro runs?
    Last edited by hreal34; 08-31-2012 at 01:56 PM. Reason: Just one question

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Macro: Deleting Rows based on date value

    Yes, you're right. If some Saturdays are working days, then the macro will require changes.

+ 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