Macro want delete the date row?

[Shetty]

Hi all,
I need to delete the rows based on the date in Col A.
I have written the following macro but it want work.
Even it does not give any errors.
My Code :
============
Sub Date_Single()
Range("A1").Select
Do While Not IsEmpty(ActiveCell.Offset(1, 0).Value)
If ActiveCell.Offset(1, 0).Value = "10/21/2003"
ActiveCell.Offset(1, 0).EntireRow.Delete shift:=xlUp
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A1").Select
End Sub
=============
All the cells in Col A is formated as date (27-Feb-04 format) and
I ensure this with the data velidation.

Can somebody please tell me what went wrong?
Regards,
Shetty.

 

[Tom Ogilvy]

Sub Date_Single()
Dim cell as Range
set cell = Range("A1")
Do While Not IsEmpty(Cell.Offset(1, 0).Value)
If Cell.Offset(1, 0).Value = DateValue("10/21/2003") Then
Cell.Offset(1, 0).EntireRow.Delete shift:=xlUp
else
set cell = Cell.Offset(1, 0)
End If
Loop
End Sub

If you delete a row, don't advance or you could skip some of the rows that
need to be deleted.

 

[Bob Phillips]

Hi Shetty,

Try this

Sub Date_Single()
Dim i As Long
i = 1
Do While Not IsEmpty(Cells(i, "A").Offset(1, 0).Value)
With Cells(i, "A")
If .Offset(1, 0).Text = "21-Feb2003" Then
.Offset(1, 0).EntireRow.Delete shift:=xlUp
End If
End With
i = i + 1
Loop
Range("A1").Select
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

based on

All the cells in Col A is formated as date (27-Feb-04 format)

probably need to use

If .Offset(1, 0).Text = "21-Feb-03" Then

of course for a single digit date, it would not be clear whether the
formatting would produce 3 or 03

 

[Bob Phillips]

Typo Tom. If the OP adopts this solution he/she should also note your
comment about missing lines because of the delete.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shetty]

HI,
Thanks for the reply. Now my macro works as intended.
Thanks again.
Shetty