Delete rows with date more than 48 hours with a condition..
 

Hi,

I have dates in cloumn K and some names in column J.

I want to delete the rows in which date is within 48 hrs and name is ABC only.

date format is '02-02-2009 13:27:00'

Column J|Column K
ABC 29-01-09
ABC 30-01-09
ABC 30-01-09
MNO 31-01-09
MNO 31-01-09
ABC 03-02-09
MNO 03-02-09
MNO 31-01-09
MNO 01-02-09
ABC 04-02-09

Delete rows with date more than 48 hours with a condition..
 

Here is one solution.

Public Sub Test()
Dim rng As Range
Dim cel As Range
Dim iCounter As Integer
Dim iLastRow As Integer
Dim dtToday As Date
Dim dtCellDate As Date

Set rng = Range("J1:J100") 'Change the J-rows as appropriate
iLastRow = rng.Cells.Count
For iCounter = iLastRow To 1 Step -1 'Workup from the bottom!
With rng
If .Cells(iCounter).Value = "ABC" Then
dtToday = Date 'Today's date
dtCellDate = .Cells(iCounter).Offset(0, 1).Value 'Date
value in K cell.
If dtToday - dtCellDate = 2 Then
.Cells(iCounter).EntireRow.Delete
End If
End If

End With
Next
End Sub

Paul



"Kashyap" wrote in message
...
Hi,

I have dates in cloumn K and some names in column J.

I want to delete the rows in which date is within 48 hrs and name is ABC
only.

date format is '02-02-2009 13:27:00'

Column J|Column K
ABC 29-01-09
ABC 30-01-09
ABC 30-01-09
MNO 31-01-09
MNO 31-01-09
ABC 03-02-09
MNO 03-02-09
MNO 31-01-09
MNO 01-02-09
ABC 04-02-09

Delete rows with date more than 48 hours with a condition..
 

Hey, its working perfect.. Thanks Paul

"Paul Wilson" wrote:

Here is one solution.

Public Sub Test()
Dim rng As Range
Dim cel As Range
Dim iCounter As Integer
Dim iLastRow As Integer
Dim dtToday As Date
Dim dtCellDate As Date

Set rng = Range("J1:J100") 'Change the J-rows as appropriate
iLastRow = rng.Cells.Count
For iCounter = iLastRow To 1 Step -1 'Workup from the bottom!
With rng
If .Cells(iCounter).Value = "ABC" Then
dtToday = Date 'Today's date
dtCellDate = .Cells(iCounter).Offset(0, 1).Value 'Date
value in K cell.
If dtToday - dtCellDate = 2 Then
.Cells(iCounter).EntireRow.Delete
End If
End If

End With
Next
End Sub

Paul



"Kashyap" wrote in message
...
Hi,

I have dates in cloumn K and some names in column J.

I want to delete the rows in which date is within 48 hrs and name is ABC
only.

date format is '02-02-2009 13:27:00'

Column J|Column K
ABC 29-01-09
ABC 30-01-09
ABC 30-01-09
MNO 31-01-09
MNO 31-01-09
ABC 03-02-09
MNO 03-02-09
MNO 31-01-09
MNO 01-02-09
ABC 04-02-09