VBA to delete all rows outside of date range

[Fawkes_]

Hey Everyone,

Im making a formatting macro with a lot of steps, one of the steps is deleting all rows that do not fall inside a set date range. It would be the date the report is being worked + 6weeks out. so I was using the date and dateadd feature to compensate for never having the exact date (report will be worked over and over until the end of time)

this is what I have thus far, I am getting a sytax error on the if and portion. Im sure there is probably a more efficient way then going line by line but I am limited in my knowledge here. Any help would as always be greatly appreciated.

Please Login or Register  to view this content.

Thank you,

Fawkes_

[davesexcel]

You need to end the line with [/code] in order for the code wrap to work.

[Fawkes_]

Sorry, ctrl-V causing issues lol, should be good now.

[davesexcel]

Possibly this should work for you,

Please Login or Register  to view this content.

[Fawkes_]

Thank you for the guidance Davesexcel. I will give this a shot

[Fawkes_]

Well here is my completed code:

Im trying to find a way to speed up the process now it takes about 10 min to complete.

Also, I was wondering if during the selections after filtering if using ("S2:S") would cause issues with anything. Im concerned with the report being larger in the future. Perhaps I should start with a last row count and use that method? I want it to select down to the last cell in the specific range, but I'm pretty sure the S2:S will go till the last possible cell not the last filled cell. What do you think?

Also, when doing the Vlook phase there is a long lull where it is linking to the file in the shared server, I was wondering if there was a way to speed that process up. 4 min of the process is the initial link to the second report that the vlook comes from

As always thank you for any guidance I this endeavor.

Please Login or Register  to view this content.

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Fawkes_]

Alright, well this isn't working.... what to do what to do.

all of the trim mods are working except for the date mod. It just blows right through without deleting anything.

I also adjusted all the final columns to LastRow and defined last row. so now that isn't an issue, however getting a code to delete something from todays date back and todays date plus 6 weeks forward... that's just not working out... any ideas?

I'm noticing in my code that Date function is not going blue, which make me think it is not calling the date properly does this sound accurate? Also, I have been searching the Date add feature and I have not been able to find any way of its use where the "mm/dd/yyyy" section is replaced with a assigned date ie sdate in this code.

maybe this isn't allowed?

Calling out to the forum gods for assistance

[Fawkes_]

Well I came up with this and it seems to work, if anyone has any ideas let me know please.

Please Login or Register  to view this content.