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.

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

Rick


"burl_h" <(E-Mail Removed)> wrote in message
news:2de132ca-9fc8-4e4d-956b-(E-Mail Removed)...
> 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.

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

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

On Feb 10, 8:06*pm, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> The cell property you need to check is NumberFormat, not just Format.
>
> Rick
>
> "burl_h" <milli...@zoominternet.net> wrote in message
>
> news:2de132ca-9fc8-4e4d-956b-(E-Mail Removed)...
>
>
>
> > 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

On Feb 10, 8:33*pm, burl_h <milli...@zoominternet.net> wrote:
> On Feb 10, 8:06*pm, "Rick Rothstein \(MVP - VB\)"
>
>
>
>
>
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > The cell property you need to check is NumberFormat, not just Format.
>
> > Rick
>
> > "burl_h" <milli...@zoominternet.net> wrote in message
>
> >news:2de132ca-9fc8-4e4d-956b-(E-Mail Removed)...
>
> > > 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