VBA to delete rows based on date

[BillDoor]

Hi all

I have the following code to delete rows based on dates, but the datea I'm using has over 100,000 records and this is taking forever, is there a fasterway to do this than looping through every line?

Please Login or Register  to view this content.

I'd thought about using autofilters, but I've heard they can be very unstable...?

thanks in advance

[Cheetahke]

Hi,

It's possible with the find command, p.e:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[JOHN H. DAVIS]

If you change your mind about autofilters.

Please Login or Register  to view this content.

I would suggest testing it by highlighting the rows instead of deleting to see how it performs.

[BillDoor]

Hi John

thanks, but I am having issues with it as I am using UK format dates - dd/mm/yyyy

Do you know anyway to get around that?

thanks again

[JOHN H. DAVIS]

Does this help? Changes to US, deletes rows, changes back.

Please Login or Register  to view this content.

Probably better ways though.

[BillDoor]

Hi

still not getting on with that

VBA really seems to hate UK dates!!

I'm also changing the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

as I want to delete anything outside of the start and end dates

[JOHN H. DAVIS]

I think xlAnd should still be your operator in that case as well. Hard to tell from this side for me, but when I tested reversing them as I suggested, it worked.

[Cheetahke]

Hi,

I've changed the code from John a little bit, can you try this? I'm used to work with Belgian dates, same format as UK dates

Please Login or Register  to view this content.