I have read up on this and I am trying to delete rows with date greater than and earlier then yesterday. I have been anything but successful but I have created a bit of code that takes forever to run with absolutely no positive results. Dates are in column A.
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To LastRow
If Cells(j, "A").Value < Date - 1 Then
Cells(j, "A").EntireRow.Delete
End If
First of allgives a value of 1 as xlUp points to the first row.
Please Login or Register to view this content.
But this would make the routine not entering the loop at all as it would beOn the other hand xlEnd would point to the last row (=1048576!). And yes, that would make the loop run for ages.Please Login or Register to view this content.
Last but not least:
the routine starts at cell A2. If that would have a date < yesterday that entire row would be deleted.
That makes cell A3 become cell A2. In the next run of the loop the routine check cell A3 and so skipping the cell which in the previous run was cell A3 but now is A2.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
When deleting rows need to start at the last row and work your way to the top
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Hi joshag
Filter the Data for dates that are before or after yesterday then delete the Visible Rows...no looping necessary...this can be done with Code...
Dates.jpg
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Thanks to everyone for their help!
Tsjallie - thank you for pointing out the errors I made!
Mike7952 - Your piece works as advertised but takes too long (I have ideas to fix this) but may incorporate this into the finished product
jaslake - I have yet to try your solution but I believe there is value in it. Lunch first, experiment after.
Hi joshag
Need help holler...
Using a filter will absolutely be faster then looping.
Turning your range into a table will also make it more reliable.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks