PDA

View Full Version : Delete columns based off of formula in another cell



ITJSI
11-30-2015, 11:01 AM
This is my first time posting, I'm desperate for help and can't find how to do this.

I have a macro that inserts a formula into cell H2, =Today()-90

I have another column "E" that has dates, some blank though. I'd like to select all of E and delete any row with a value that is greater than the result of the formula in H2.

I'm a novice and this has me stumped, I appreciate any help.

Thanks

ITJSI
11-30-2015, 11:41 AM
I am looking at this wrong. I have a code that works but is not handling blanks well.


Dim LR As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = Range("E" & rows.Count).End(xlUp).Row

For I = LR To 1 Step -1
If Range("E" & I).value <= Date - 90 Then rows(I).Delete
Next I

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

It seems to stop when a cell in column E is blank. I cannot figure how to ignore the blanks.

SamT
11-30-2015, 03:13 PM
Add a condition to the If statement.


For I = LR To 1 Step -1
With Range("E" & I)
If .value <> "" And Value <= Date - 90 Then rows(I).Delete
End With
Next I