date based delete

[mani_singh]

hi i have a large set of data, with a data in column a & column e & some text in column F

i need macro to remove rows where the date in col a is earlier than 6 months before the date in column e & column f has the text "a/g or "g"

thanks

 

[Jonmo1]

Try this

SAVE YOUR SHEET FIRST!!!!

Sub Test()
Dim LR, i
Dim x
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 1 Step -1
    If IsDate(Cells(i, "A")) And IsDate(Cells(i, "E")) Then
        If Cells(i, "A").Value < DateAdd("m", -6, Cells(i, "E").Value) Then
            If Cells(i, "F").Value = "a/g" Or Cells(i, "F").Value = "g" Then
                Rows(i).EntireRow.Delete
            End If
        End If
    End If
Next i
End Sub

Hope that helps..

 

[mani_singh]

thanks i'll try it now, i was half way there when i got your responce! thanks

 

[mani_singh]

doesn't make any changes to the data!

any ideas?

 

[mani_singh]

i've up this together

Sub runme()


LastRow = Sheets("data2").Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For i = 3 To LastRow


 With Cells(i, "E")


If .Value > .Cells(i, "A") + Month(target) + 6 Then
If Cells(i, "F").Value = "A/G" Or Cells(i, "F").Value = "G" Then
Rows(i).EntireRow.Delete

End If
End If
End With
Next i

Application.ScreenUpdating = True

End Sub

it doesn't work butwhen i remove the if statement for looking for the R or A/R then it works!

any help?

 

[Jonmo1]

that's pretty far changed from the way I posted it...

Try this..

Sub Test()
Dim LR, i
Dim x
With Sheets("data2")
LR = .Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 1 Step -1
    If IsDate(.Cells(i, "A")) And IsDate(.Cells(i, "E")) Then
        If .Cells(i, "A").Value < DateAdd("m", -6, .Cells(i, "E").Value) Then
            If Ucase(.Cells(i, "F").Value) = "A/G" Or Ucase(.Cells(i, "F").Value) = "G" Then
                .Rows(i).EntireRow.Delete
            End If
        End If
    End If
Next i
End With
End Sub

 

[mani_singh]

its what i was writing when you posted the first response! i'll give the second a go

 

[mani_singh]

no change to the data!

e.g.test data
in col A = 1/1/08
in col e = 1/10/08
in col f = A/R

this should be deleted as the date in col a is OVER 6 month before date in col E and col F has a text string of "A/R"

 

[Jonmo1]

and col F has a text string of "A/R"

Your post (and the code you wrote) used A/G ?? which is it? Adjust the code accordingly..

Are the dates in Column A and E VALID Excel Dates ?
to verify, use these formulas in available columns ANYWHERE on your sheet..
=ISNUMBER(A1)
=ISNUMBER(E1)
Fill both down to the end of the data

Do they return TRUE or FALSE?

 

[mani_singh]

ignore that, both work its the data i'm using thats the problem

thanks !