Macro want delete the date row?

  • Thread starter Thread starter Shetty
  • Start date Start date
S

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.
 
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.
 
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)
 
based on
All the cells in Col A is formated as date (27-Feb-04 format)
Click to expand...

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
 
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)
 
Back
Top