Macro to delete entire rows where O2:O does not equal a date

**rain4u** · 04-19-2011, 08:14 PM

Hi
Can someone suggest to me a good efficient macro that would delete rows where O2:O does not equal a date. So if I have this information in the column O as below:

Please Login or Register  to view this content.

Then after the macro run it would only leave me header row and the rows with dates like this.

Please Login or Register  to view this content.

Any ideas?

Cheers
Rain

**tlafferty** · 04-19-2011, 08:25 PM

Please Login or Register  to view this content.

**rain4u** · 04-20-2011, 07:52 PM

Hi
Thank you for answering.
Offered code does not remove all unwanted rows. Please see attached xls

Any ideas

**nimrod** · 04-20-2011, 08:05 PM

Offered code does not remove all unwanted rows.

If you are going to delete by looping thru rows you MUST loop from the bottom and move up OR... you can delete all rows of certain type at one time like the following code does.
This code deletes all rows that have a constant that is not a number or date.

FAST ONE STEP METHOD

Please Login or Register  to view this content.

.
.
.
.
LOOP METHOD (slower but works)

Please Login or Register  to view this content.

**rain4u** · 04-20-2011, 10:37 PM

The fast version will do. Thank you. Column O will always have either a date or text in it. It will never contain just numbers like "1" or "2". And even if it will have a number as the first character lets say number 2, it would always have some text to go along with it, so it would be classed as a text. Lets say "2 will be shipped". So the fast code fits the bill!
Thank you!

Cheers
Rain