Macro: Deleting Rows based on date value

**hreal34** · 08-31-2012, 12:59 AM

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

**stojko89** · 08-31-2012, 01:43 AM

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?

**nilem** · 08-31-2012, 02:06 AM

maybe something like

Please Login or Register  to view this content.

**hreal34** · 08-31-2012, 02:18 AM

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.

**stojko89** · 08-31-2012, 02:23 AM

I tryed. And it delete's too much

- my code.
nilem, I tryed yours and it didn't work for me.

**nilem** · 08-31-2012, 02:34 AM

Originally Posted by stojko89

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)

**hreal34** · 08-31-2012, 01:55 PM

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?

**nilem** · 08-31-2012, 02:08 PM

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

Macro: Deleting Rows based on date value

LinkBack

Thread Tools

Rate This Thread

Display

Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Re: Macro: Deleting Rows based on date value

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions