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:

HEADER
Complete
Complete
Date in past
03/05/2011
Complete
Complete
No info
Quarrantined
Complete
Complete
Complete
Complete
Complete
18/04/2011
18/04/2011
Complete
Complete
Complete

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

HEADER
03/05/2011
18/04/2011
18/04/2011

Any ideas?

Cheers
Rain

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

Sub DelRows()
Dim lngLastRow As Long
lngLastRow = Range("O65536").End(xlUp).Row


For Each c In Range("O2:O" & lngLastRow)
    If Not IsDate(c.Value) Then c.EntireRow.Delete
Next

End Sub

**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

Public Sub DeleteStringsLeaveDates()
   Range("O2:O" & Rows.Count).SpecialCells(xlCellTypeConstants, 22).EntireRow.Delete
End Sub

.
.
.
.
LOOP METHOD (slower but works)

Public Sub LoopAndDelete()
   Const TargCol As String = "O"
      Application.ScreenUpdating = False
      BottomRow = Cells(Rows.Count, TargCol).End(xlUp).Row
   
      For iRow = BottomRow To 2 Step -1
         If Not IsDate(Cells(iRow, TargCol).Value) Then Cells(iRow, TargCol).EntireRow.Delete
      Next iRow
      
       Application.ScreenUpdating = True
End Sub

**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