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.

--
Regards,
Tom Ogilvy


"Shetty" wrote in message
om...
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.

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)

"Shetty" wrote in message
om...
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.

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



--
Regards,


"Bob Phillips" wrote in message
...
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)

"Shetty" wrote in message
om...
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.

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)

"Tom Ogilvy" wrote in message
...
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



--
Regards,


"Bob Phillips" wrote in message
...
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)

"Shetty" wrote in message
om...
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.

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

"Bob Phillips" wrote in message ...
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)

"Tom Ogilvy" wrote in message
...
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



--
Regards,


"Bob Phillips" wrote in message
...
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)

"Shetty" wrote in message
om...
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.