VBA to loop through and delete all date except Yesterdays date.

hi,

I have column with different dates in column A. I am trying to delete all rows for which date is not equal to yesterdays date.
how can we achieve that?

Thanks,
Shiva

Please Login or Register  to view this content.

Thanks for the code..

I am getting run time error 424 " Object Required "
Line noted in red below.

Please Login or Register  to view this content.

i also tried this logic but not working

Please Login or Register  to view this content.

sheets("Sheet20").UsedRange.Columns(1).Rows.Count To 1 Step -1

It is working.. But other way around.

Sorry for not being clear. I want VBA to keep only yesterdays Date and delete all other date.

Eg.

07/07/2014
07/08/2014
07/09/2014

I want VBA to delete all other date but to keep 07/08/2014 which is of yesterdays date as on today. So this date would change everyday.

Thanks,

Thanks Natan. Few modification and I got it working

Please Login or Register  to view this content.

Hi shiva_reshs,

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Please Login or Register  to view this content.

HTH

Robert

Thanks Robert ...