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

[Kashyap]

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

 

[Paul Wilson]

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]

Hey, its working perfect.. Thanks Paul

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