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

In the below table I want to delete rows with dates more that 48 hours in
column K if column J="ABC"

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..
 

48 hours is two days. Try this macro:

Sub rowkiller()
Dim n As Long
Dim d As Date
Dim lt As String
Dim dte As Date
d = Date
n = Cells(Rows.Count, "J").End(xlUp).Row
For i = n To 1 Step -1
lt = Cells(i, "J").Value
dte = Cells(i, "K").Value
If lt = "ABC" And d - dte 2 Then
Rows(i).Delete
End If
Next
End Sub

The result on your posted data is:

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


--
Gary''s Student - gsnu200831


"Kashyap" wrote:

In the below table I want to delete rows with dates more that 48 hours in
column K if column J="ABC"

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..
 

Hi, I tried this, but there is an error in ' dte = Cells(i, "K").Value



"Gary''s Student" wrote:

48 hours is two days. Try this macro:

Sub rowkiller()
Dim n As Long
Dim d As Date
Dim lt As String
Dim dte As Date
d = Date
n = Cells(Rows.Count, "J").End(xlUp).Row
For i = n To 1 Step -1
lt = Cells(i, "J").Value
dte = Cells(i, "K").Value
If lt = "ABC" And d - dte 2 Then
Rows(i).Delete
End If
Next
End Sub

The result on your posted data is:

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


--
Gary''s Student - gsnu200831


"Kashyap" wrote:

In the below table I want to delete rows with dates more that 48 hours in
column K if column J="ABC"

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..
 

What's in that cell?



Kashyap wrote:

Hi, I tried this, but there is an error in ' dte = Cells(i, "K").Value

"Gary''s Student" wrote:

48 hours is two days. Try this macro:

Sub rowkiller()
Dim n As Long
Dim d As Date
Dim lt As String
Dim dte As Date
d = Date
n = Cells(Rows.Count, "J").End(xlUp).Row
For i = n To 1 Step -1
lt = Cells(i, "J").Value
dte = Cells(i, "K").Value
If lt = "ABC" And d - dte 2 Then
Rows(i).Delete
End If
Next
End Sub

The result on your posted data is:

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


--
Gary''s Student - gsnu200831


"Kashyap" wrote:

In the below table I want to delete rows with dates more that 48 hours in
column K if column J="ABC"

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



--

Dave Peterson

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

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'



"Dave Peterson" wrote:

What's in that cell?



Kashyap wrote:

Hi, I tried this, but there is an error in ' dte = Cells(i, "K").Value

"Gary''s Student" wrote:

48 hours is two days. Try this macro:

Sub rowkiller()
Dim n As Long
Dim d As Date
Dim lt As String
Dim dte As Date
d = Date
n = Cells(Rows.Count, "J").End(xlUp).Row
For i = n To 1 Step -1
lt = Cells(i, "J").Value
dte = Cells(i, "K").Value
If lt = "ABC" And d - dte 2 Then
Rows(i).Delete
End If
Next
End Sub

The result on your posted data is:

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


--
Gary''s Student - gsnu200831


"Kashyap" wrote:

In the below table I want to delete rows with dates more that 48 hours in
column K if column J="ABC"

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



--

Dave Peterson