RE: VBA Macro Needs Tweaking to Delete Rows Where Date As String is Less Than Today's Date

I need some help tweaking the macro I've written below. I'm trying to delete all the rows associated with a company listed in column A, if that company has a "Meeting Date" (also listed in column A) that falls outside of an acceptable date range. The acceptable date range is 7 calendar days, beginning from whatever "today's" date is (i.e. this is a daily report). This means that there are three conditions under which a company should have all its rows deleted:

1. The Meeting is AFTER today's date + 7 calendar day range (i.e. currently out-of-scope)
2. The Company shows NO INFORMATION in column B (i.e. faulty data record)
3. The Meeting Date is BEFORE Today's Date (i.e. the meeting has passed)

I've been able to get the Macro to work for conditions 1 and 2, but I can't get it to work for condition 3. I attempted to edit the script to delete the rows associated with a company listed in column A that have a Meeting Date (e.g. MEETING DATE - 2013-07-31) by defining "Dim FirstDay as Date" and adding "Or Right(Cells(i, "A"), 10).Value < FirstDay Then" to my conditional statements, but I couldn't get it to work. I posted the part of my code that works below. I'm pretty sure the problem is getting the "Meeting Date" string and "today's date (Now)" formats to match-up correctly, so that they can be compared properly.

I've also attached the Sample.xlsm file to this posting. It has three tabs. The first tab shows a sample of the data that contains all three conditions being tested for. The second tab shows the results of my macro posted below. That's how I want it to look. I'm always open-minded to more efficient ways of getting there. Finally, tab three shows how I want the macro to work. If today's date is 8/5/2013, then any companies with Meeting Dates below this should be deleted (i.e. 7/31/2013), any companies with Meeting Dates falling outside the 7 day range should be deleted (i.e. 8/13/13 and later), and any companies showing no data (i.e. Company A). As for general information, the number of rows will always change on this report, but the number of columns will remain constant at twelve (A:L). The only information relevant for our purposes, though, appears in columns A and B.

Please Login or Register  to view this content.