Delete Row Based on IF Equation

[MSmithson]

Hello all, need a little help completing a macro that deletes rows based on an IF equation:

If the value in the Col marked “End Date” in the header is less than the value in the Col marked “Output Effective Date” then delete that row. Would need to loop through the entire worksheet (several thousand rows). Something like: IF(value in Col “End Date” is LESS THAN value in Col “Output Effective Date”) THAN delete row

“End Date” and “Output Effective Date” are not always in the same column, which is why I want to use the header (row 1) as the identifying variable.

The only exception is if "Output Effective Date" is blank. In this case, the row should NOT be deleted.

Any help is most appreciated – thanks!

[JBeaucaire]

You can actually do this manually with an AUTOFILTER in a matter of seconds.

1) In an empty column put in a formula to do the data comparison for you row by row. So if:
---Column D is End date
---Column E is Output Effective Date
---Column T is empty, put this in T2 and copy down:

=IF(AND($E2<>"", $D2<$E2), "Delete", "Keep")

2) Click on row1
2) Select Data > Filter > AutoFilter to turn it on
3) In the column T you added use the drop down and select "Delete"
4) Highlight all the visible rows and delete them
5) Turn off the Autofilter the same way you turned it on
6) Clear the added column (T)

[MSmithson]

Thanks JBeaucaire, any way to do this with VBA code? I'm trying to incorporate this function into a larger macro. Thanks!

[JBeaucaire]

Of course you have a good sample workbook to design a macro off of, yes?

[MSmithson]

Yes, attached is a sample report. For example, row 6 (highlighted) should be deleted because the End Date comes before the Output Date. You'll also notice that a few Output Date fields are blank. In this case, nothing should happen (that row should not be deleted).

Thanks so much for your help!

[JBeaucaire]

So, based on your sample, EVERY row is deleted except 16, 17 and 18 because all of them have END DATES that are less the OUTPUT EFFECTIVE DATE?

[MSmithson]

Sorry, perhaps I should have formatted the date with 4 digest instead of 2 - those dates are 2042. So, only row 6 should be deleted because the end date comes before the output effective date.

[JBeaucaire]

In E2, the date is 12/31/10.
In C2 the date is 9/28/42.

E2 is less than C2 to row 2 would be deleted.

The same is true for every row except the rows where C is blank.

If this is incorrect you will have to change the data or change the words you're using to describe what to do. You said if "END DATE" is earlier than "OUTPUT EFFECTIVE DATE" then delete the row. That's true for your entire sample.

[MSmithson]

Sorry about that - I see what you mean. Attached is a new workbook. Row 6 should be deleted because the output date (7/1/2010) comes after the End Date (6/2/2010). Also, rows 16, 17, and 18 should not be deleted even though the output date field is blank. Help is very appreciated!

[JBeaucaire]

This is basically the same thing I told you above recorded into a macro:

Sub RemoveRows()
'Uses a blank column to create an AUTOFILTER set of formulas to delete all unwanted
'rows all at once.
Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("M1") = "key"
    Range("M2:M" & LR).FormulaR1C1 = "=IF(AND(RC3>0, RC5 < RC3), ""Delete"", ""Keep"")"

    With Range("M1")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Delete"
        Range("M2:M" & LR).Delete xlShiftUp
        .AutoFilter
        .EntireColumn.ClearContents
    End With

End Sub

[MSmithson]

Thanks so much for your help! I'll give this a try and let you know. Appreciate it!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.