On Feb 10, 8:33*pm, burl_h wrote:
On Feb 10, 8:06*pm, "Rick Rothstein \(MVP - VB\)"





wrote:
The cell property you need to check is NumberFormat, not just Format.

Rick

"burl_h" wrote in message

...

I'm having a problem with the following code, for some reason I can't
get the correct syntax.

Sub delete_not_date
Dim datarng As Range
lastrow = Cells(rows.Count, "B").End(xlUp).Row
Set datarng = Range("b2:b" & lastrow)
* * *For i = lastrow To 1 Step -1
* * * * *For Each cell In datarng
* * * * * * *If Not cell.Format = "mm/dd/yyyy" Then
* * * * * * * * *cell.EntireRow.Delete
* * * * * * *End If
* * * * *Next cell
* * *Next i
End Sub

My problem is the date format, I tried many variations but can't get
anything to work.

I did check the format on the cells and found them to be a custom
format written as "mm\/mm\/yyyy". The data originated from a Crystal
report, hence perhaps the weird date format.

The objective is to delete all rows that don't contain a date value.

Thanks.- Hide quoted text -

- Show quoted text -

Rick,

Thanks for your solution, it worked fine. I did however have to change
the format to "mm\/dd\/yyyy" to make it work, the little quirk with
Crystal reports no doubt.

Thanks
burl_h- Hide quoted text -

- Show quoted text -

Dave,

Yes that was a typo on my part "mm\/mm\/yyyy" should be "mm\/dd\/yyyy"

I did try your solution and it worked great, thanks very much for
everyone's help.

Thanks
burl_h