delete all rows where cell is not date format

[burl_h]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Dave Peterson]

Along with the comment that Rick made, your custom format of mm/\mm/\yyyy looks
really weird.

Was that a mistake in the post?

And you don't want to loop within your loop.

I'd use:

Option Explicit
Sub delete_non_dates()
dim iRow as long
Dim FirstRow as long
dim LastRow as long
with activesheet
firstrow = 2 'or 1?????
lastrow = .Cells(.rows.Count, "B").End(xlUp).Row
for irow = lastrow to firstrow Step -1
If .cells(irow,"B").numberformat = "mm/dd/yyyy" Then
'keep it
else
.rows(irow).delete
end if
next irow
end with
End Sub

 

[Dave Peterson]

And just in case...

Change this line:
If .cells(irow,"B").numberformat = "mm/dd/yyyy" Then
to
If lcase(.cells(irow,"B").numberformat) = lcase("mm/dd/yyyy") Then

 

[burl_h]

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

Rick











- 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

 

[burl_h]

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