delete all rows where cell is not date format
 

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.

delete all rows where cell is not date format
 

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.

delete all rows where cell is not date format
 

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



burl_h wrote:

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.

--

Dave Peterson

delete all rows where cell is not date format
 

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



Dave Peterson wrote:

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

burl_h wrote:

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.

--

Dave Peterson

--

Dave Peterson

delete all rows where cell is not date format
 

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

delete all rows where cell is not date format
 

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